SSIS - Limiting Concurrent Connections

Question

Hi Folks, I am using SSIS to connect to a legecy mainframe database and this allows only 5 concurrent connections at a time. I have a dataflow task with many tables to transfer and it kicks outs because of this limitation. I have split up the Data Flow task into seperate data flows and this is working for the moment, but it is not optiomal as they need to be sequenced and 1 large transfer in a flow is holding up subsequent transfers. Anyone any idea of how to limit the number of connections in a single data flow, I had a look at using the Engine Threads but this did not make any difference.

Any help much appericated.

Answer 1

The connection object you are using for your tasks should have a property named 'RetainSameConnection'. This should cause the same connection to be used across all tasks. At least this is true for OLEDB connection types. I don't know if ADO.NET connections have the same property. They probably do.

Here is an article for more information: http://munishbansal.wordpress.com/2009/04/01/how-to-retain-same-data-connection-across-multiple-tasks-in-ssis/

Answer 2

Could you use a loop to implement a 'retry' method if a connection cannot be obtained?