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

Filed under:

Hi

I have the following SSIS package:

alt text

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

Related posts about ssis