SSIS - connection management inside the loop

Question

I have the following SSIS package:

alt text http://www.freeimagehosting.net/uploads/5161bb571d.jpg

The problem is that in the Foreach loop, the connection opens and closes for each iteration.

When starting SQL Profiler, I see a series:

• Login to the audit system
• RPC: completed
• Exit audit

The duration of entry and RPC, which actually does the job, is minimal. However, the duration of the exit from the system is significant, and it takes several seconds. This makes the JOB run very slowly - it takes many hours. I get the same problem when starting either on a test server or on a standalone laptop.

Can anyone suggest how I can change the package to improve performance?

In addition, I noticed that when you run the package from Visual Studio, it looks as if it continues to work so that the component blocks become amber and then green, but in fact, all processing is completed and the SQL profiler is disabled?

Thank,

Rob.

Answer 1

Have you tried to complete a data flow task in parallel with a serial one? You can most likely break your loops so that you can run each “set” in parallel, so although it can be expensive to enter / exit, you will do it N times at a time.

Answer 2

SQL Server . , , , for-loops, ?

RPC ( ), .

, - :

for each Facility
 for each Stock
  update Qty

, ( SQL RPC ), :

 update Qty
 from Qty join Stock join Facility
 ...

RPC, RPC API ( ), , , ( record = someRecord).

Answer 3

?

, , . "RetainSameConnection" "" False. . , :

• RPC:
• RPC:
• RPC:
• RPC:
• RPC:
• RPC:
• ...

.