I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
Any ida of what this depends on?
regards Andreas
I don't know the answer but I have exactly the same happening for a package I've just created that just copies data from an Ingres database to SQL Server.
Visual studio 45 mins, scheduled job 2 hrs 5 mins
Shocking!
|||You both have conflicting results, so here are some points to think about.
Visual Studio includes debugging, which is slower. You can run without, Ctrl+F5. This should be faster than the default F5 execution behaviour. Debugging can have a high overhead due to all the extra messages. Excessive logging can also have an impact, this is similar to debugging in many resects due to the extra chatter going on to give progress information.
Unless the Visual Studio session and the SQL Server Agent used for the job are on the same local machine, there is a change in execution location between the two methods. This can have a significant impact on performance depending on your source and destination locations. Data may have to travel over the network to the server or workstation, and this can vary greatly.
|||Wicket wrote:
I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packages\MSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
Any ida of what this depends on?
regards Andreas
That sounds strange. What happens if you execute the package using dtexec.exe?
If it works quicker using dtexec (which I would expect) then use a command-line sub-step in the Agent job rather than the SSIS sub-step. In fact you should be doing this anyway because you get more debugging information. There's an article about this on wiki.sqlis.com but that site is down at the moment so I can't link to it.
-Jamie
:
|||
Is this the article you mean-
Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)
I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.
|||I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?
I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job. 
Is this the article you mean-
Comparing Overhead On The Execution Methods
(http://www.sqlis.com/default.aspx?84)I think the main site and wiki are both up, although with my internet connection quality today they may was well not be.
No. I mean this one: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html (site is up now)
-Jamie
|||
I'm running Visual Studio on the server itself, not a workstation, so I'm not hopping round the network when running the package from VS but that's beside the point isn't it?
I've stored the package in msdb, scheduled it to run using an agent job using the SQL Server service acount and it takes nearly 3 times as long, I'd expected it to run slow in VS but not as a scheduled job.
Did you try running it using dtexec?
Is SQL Agent running as a different user?
-Jamie
|||
Is SQL Agent running as a different user?
-Jamie
No, I don't know how to but I'll find out and give it a try.
Yes it is.
|||I'm running the package on the server in each scenario, so no extra networktraffic should take place in any case I think. And the same package (non debug) is used all the time. However, I do run the job as a different user. Should this have any effect?
I'd love to run the package as a command line step, but when I try to do that, I can't seem to get it to read the config-file I'm giving in the command line. Instead an error message complains about that the configuration file cannot be found at the original location (the location given in the settings inside the package). Shouldn't this setting be overridden by the one on the command-line?
regards Andreas
