SSIS Single Source & Destination Data Flow Task Connection Open Close

Question

Forgive me if I missed a thread on this. I looked over similar questions, but none this specific. I have been reviewing MS documentation and I think I found the answer, however I need clarity.

I have an SSIS ETL package I am creating in Visual Studio 2015. I have a single source and destination table, however, the data is rather large (16M+). So I found I can split this data up by a column within the data to mostly equal parts of 3M or so records per his Data Point (Location). I am currently creating multiple DFT's (Data Flow Tasks) to handle this, in no particular order, to load each Location DFT into a single destination in a SQL Server 2016 table.

My question is: When each DFT completes and moves to the next DFT, does the connection to the source reset?

My research has so far led me to the Transaction Option setting (Supported/Not Supported), but the MS Documentation is hard to decipher as it only speaks of committing rows to the destination or rolling back.

This does not seem to address if the connection remains open between DFT's or if the connection is at the Parent level or per DFT in use.

Thanks for any clarity on this.

Answer 1

You can choose the behavior of connection by setting RetainSameConnection property. Refer to this article.

RetainSameConnection is a property of an OLEDB Connection Manager. The default value of this property is FALSE. This default value makes SSIS execution engine open a new OLEDB connection for each task that uses the connection and closes that connection when the task is complete

In your case, you can test whether this property helps you or not. Running parallel DFT on same destination table might cause inserts to happen in un-orderly fashion. So make sure you're not impacted by it.