SSIS - Connection Management Within a Loop

Question

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 Logout

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.

Answer 1

Have you tried doing the following?

In your connection managers for the connection that is used within the loop, right click and choose properties. In the properties for the connection, find "RetainSameConnection" and change it to True from the default of False. This will let your package maintain the connection throughout your package run. Your profiler would then probably look like:

• Audit Login
• RPC:Completed
• RPC:Completed
• RPC:Completed
• RPC:Completed
• RPC:Completed
• RPC:Completed
• ...
• Audit Logout

With the final Audit Logout happening at the end of package execution.

Answer 2

Have you tried running your data flow task in parallel vs serial? You can most likely break up your for loops to enable you to run each 'set' in parallel, so while it might still be expensive to login/out, you will be doing it N times simultaneously.

Answer 3

SQL Server is most performant when running a batch of operations in a single query. Is it possible to redesign your package so that it batches updates in a single call, rather than having a procedural workflow with for-loops, as you have it here?

If the design of your application and the RPC permits (or can be refactored to permit it), this might be the best solution for performance.

For example, instead of something like:

for each Facility
 for each Stock
  update Qty

See if you can create a structure (using SQL, or a bulk update RPC with a single connection) like:

 update Qty
 from Qty join Stock join Facility
 ...

If you control the implementation of the RPC, the RPC could maintain the same API (if needed) by delegating to another which does the batch operation, but specifies a single-record restriction (where record=someRecord).