I load SSIS package using following code:
Application app = new Application();
Package pac = app.LoadFromSqlServer(packageName, serverName, null, null, null);
For simple package containing 2 tasks this code executes about 20 seconds.
If I load old version (SQL2K) package from SQL2000 then it takes 5 seconds.
Is any way to increase loading speed for SSIS packages?
Do you have SSIS service running? The service caches SSIS component information; in the absense of the service it may take long time (although it is usually 2-3 seconds on reasonable hardware) to enumerate them when loading the package.|||Thank you Michael.
I carry out additional experiments with realworld package (contains 11 tasks) at another computer.
When I start this experiment I have to wait 1.5 minutes to load package every time. Then I examine if SSIS running. I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds. When I start it second time it takes about 2 seconds. This is good result. But is there ability to increase loading speed at first time? Will be loading perfomance improved if such delay caused internal implementation of SSIS?
Thank you Martin
This helps.
BTW, BOL says:
"Validating the package before it runs is a way of finding some errors before execution starts. However, it processes the package to find errors, and if no errors are found, the package runs. Because this goes through the package two times, validating a package increases the amount of processing for the package, so should be used only when necessary."
Alexey Rokhin wrote:
... I discover SSIS don't running after SP1 was applied (widely known SP1 bug). I installed post SP1 cumulative hotfixes and continue investigation. Now package loading first time during 30 seconds...
The bug you quote is described in http://support.microsoft.com/kb/918644. While the hotfix fixes the service startup failure, it can't fix the cause of the problem - long time to start the service due to network configuration that timeouts requests to CRL. So the service still takes 30+ seconds to start (which caused to fail before the hotfix, this later part is now fixed).
To avoid this 30+ delay you should either change the SSIS service to autostart (thus moving the delay to the computer boot time), or better fix the network configuration as described in the KB (although it might be very specific to your environment) to either allow access to CRL, or to quickly fail and return failure status to an application accessing CRL.
|||Thank you, Michael.
In production environment SSIS service will autostart.
Alexey,
IMHO, a good practice is defining a Events class and so, debugging the code and so you'll find issues faster, i.e with F11...
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)
Public Class EventsSSIS
OnError
OnPreExecute
..
..
