Tag Info

New answers tagged

2

The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table. Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would ...


4

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently). If you are using traditional SQL then you probably don't need 1), but you might need 2). Your ETL must be running in ...


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...


0

Before you go re-inventing the wheel there, have you actually tried the XML Source (in Data Flow)? I created a 50GB XML file (40 million users with 20 skills each totalling 800 million rows) and it worked perfectly well: This was on my laptop with 16GB RAM using SQL Server 2014 and there were no out-of-memory errors. This does suggest the XML Source ...


6

(From the screenshot) I see an unusual configuration where you have SQL Server's Min and Max memory set the same. Leave min memory as default. See my answer to SQL Server Min Server Memory. To determine ideal memory, refer - How to determine ideal memory for instance? and SQL Server Maximum and Minimum memory configuration We have SSIS running packages on ...


3

How much memory does SSIS need? (The real answer is: It depends.) However, Jonathan Kehayias has a formula that is pretty good, or so I think, since it works great for me. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/ The simple formula for reserving space for needs other than SQL Server in Windows is: ...


0

You can : Method1 1) Create a trigger, in case if its the concern for a single table that user A should be not be conflicting. Therefore you can create the trigger which should restrict user A from the current permission it have , that you would like to restrict. 2) Once done with creating trigger, you will need to create an SQLAgent job which enables ...


0

Create a script component as a data source. You can probably re-use a lot of your code that you used to create the script task. Then you can set your batch size.


1

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


2

If I get rid of the data representing 95 % of the table, I should have no further trouble filtering the rest of the data. In that case, separating that 95% of the data is way to go. If you want to stay with SSIS, the obvious solution is to redirect those records to a another flat file (can also be a separate db) during import. Specifying this flat file ...


1

You should calculate this during your ETL process or create a calculated column on your fact table. Then, aggregations will be easier and queries will be somewhat faster than calculating the values at runtime.


0

Not sure if this is a proper answer, but I'm too low rep at the moment to just comment. I have similar tasks that I achieve by comparing the load table to the target table by stored proc ( execute sql step in your ssis package ). Something like: With Inactive as ( -- returns doctor id's that appear in the Load table but NOT in the target table select ...


1

How do I check to see if the record is not in .CSV file but it exists in SQL database? Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a ...


2

Here is a link on SQL Server 2014 for you to Grant Permissions to Integration Services Service https://msdn.microsoft.com/en-us/library/Hh213130(v=sql.120).aspx The first step is: Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. The following link for DCOM settings were last reviewed in 2006, but ...


0

I fixed this issue by changing the connection name of both source and destination in Package itself and I tried to run the job manually by clicking Start Job at Step and it worked fine :)


1

Since this seems to be a data warehouse, is / can the recovery model be bulk logged (or simple)? That in addition with using TABLOCK hint on table_b could help a lot since the inserts could be minimally logged. I found a blog post by Itzik Ben-Gan on minimally logged inserts that could help.


1

Problem has been fixed. So I'll try to decribe solution to this problem and maybe for someone it'll be useful and save time. In the beginning we have an error message from wizard: "Failed to deploy the project. Try again later. (Microsoft SQL Server, Error: 27118)" Let's see detailed error, to do this just need to click on "Show technical details ...


3

Please use Database Mail instead of SQL Mail. SQL Mail had security issues on several layers and in SQL 2005 SP2 had 'inbound emails' removed. They then further removed support for it and it is now in the depreciation list. Database Mail is the solution you are looking for. It passes audits easily, has lots of documentation, and is fully supported by ...


0

There are two choices where to store SSIS Packages: the file system or the database. Within each of those, there are two variants. The File System supports anywhere you want to put packages. The Package Store is just a well known location of %Program Files%\Microsoft SQL Server\NNN\DTS where NNN matches your version. The challenge with the Package Store is ...


1

From BOL If a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction. If I have a sequence container and it is marked ...


0

I was getting the same error, i was using a proxy account with my own database security credentials, and the password had changed. Once i changed it in the security ->credentials, the package started working..


0

The solution was that job needed to has an access to the default folder (on the server) C:\Users\Default\AppData\Local\Microsoft\SQL Server\Smo My colleage solve it and according to him it is some kind of bug which is patched in newer version of SQL Server.



Top 50 recent answers are included