March 16, 2012 at 5:57 am
I'm developing an SSIS Package that has gotten quite large.
I have over 60 transformation to the AS400 with a combined total of 60,000,000 rows.
If I click on a task it takes forever to respond.
I first noticed that it was taking forever to validate task upon opening the project.
Yesterday I started noticing that it would take forever to respond when I tried to draw a precedence constraint.
Today I can barely do anything. I started out ok but now, I'm dead in the water.
Initially I thought that the problem might be compounded by the fact that my VPN COnnection is showing very slow throughput.
I created a new Project and created one transformation and the performance was ok.
Could it be that the project is so large and the VPN is making the problem worse.
I worked from home last night and no issues.
Should I split the Package up?
Can I do this in disconnected edit mode like in DTS, if that exists in SSIS?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2012 at 8:12 am
It is entirely possible the VPN is only making the problem worse..
That is a lot of transforms.. It might make sense to see if breaking the package down to more manageable chunks would make sense. I would recommend not taking that too far, meaning go from one package to two or three.
I seem to remember a work offline mode but I haven't tried it, check under the File menu option.
CEWII
March 18, 2012 at 5:11 pm
I have seen this, too.
The VPN will only make it worse, but I've seen slowdowns in large packages and also in projects with a large number of packages.
Given the number of packages I had was the bigger problem I haven't tried splitting a package. But it is possible to go too far.
March 19, 2012 at 4:09 am
Been there with over a hundred tansformations. It is now a control package that calls eight packages which divide the tables fairly evenly between them in alphabetical blocks, so I have TransferA-C, TransferD-F etc.
Maintenance is sooooo much quicker and the packages could be allowed to run concurrently although I keep them as one linked flow and let multiple transfers within each package run concurrently (tune with max concurrent executables).
I did it by copying in file manager the dtsx file to make as many packages as needed and naming them appropriately then open up the project in Visual Studio and from the solution explorer, right click the project and include existing item.
Then (the slow bit) edit each package and delete the transforms not needed in that package.
March 19, 2012 at 6:28 am
I need to split the package for sure.
I'm going to open another post asking if there is a way to edit the package in the DTS equivalent disconnected edit mode.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply