SSIS - Connection Management Within a Loop
Posted
by Rob Bowman
on Stack Overflow
See other posts from Stack Overflow
or by Rob Bowman
Published on 2010-05-19T17:02:30Z
Indexed on
2010/05/19
17:10 UTC
Read the original article
Hit count: 227
ssis
Hi
I have the following SSIS package:
The problem is that within the Foreach loop a connection is opened and closed for each iteration.
On running SQL Profiler I see a series of:
Audit Login RPC:Completed Audit Lout
The duration for the login and the RPC that actually does the work is minimal. However, the duration for the logout is significant, running into several seconds each. This causes the JOB to run very slowly - taking many hours. I get the same problem when running either on a test server or stand-alone laptop.
Could anyone please suggest how I may change the package to improve performance?
Also, I have noticed that when running the package from Visual Studio, it looks as though it continues to run with the component blocks going amber then green but actually all the processing has been completed and SQL profiler has dropped silent?
Thanks,
Rob.
© Stack Overflow or respective owner