RetainSameConnection Properties on SSIS

We can see a connection manager as a factory creating connections.Each time a connection manager is used by an SSIS component, a new connection is created. If we set RetainSameConnection to true, we tell the connection manager to create only one connection and hold on to it as long as the package runs. This prevents temporary tables or transaction to be dropped.

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 and close that connection when the task is complete.

We can set the property value to TRUE and then it will open just one OLEDB connection with a server and keep it alive till the end of the package execution. The property can be set via the Properties window for the OLEDB Connection Manager.

Here I am taking an example where I am using these properties. I am taking a Foreach loop container. In a folder I am having more than 1400+ files and I want to insert a record to in table which having the information of file name.

Take Foreach loop container

  

Take the Enumerator is file enumerator.

  

Configure the collection and select the folder and file extension and checked Name only because we are storing the name. Select the Traverse subfolder. It means if folder contain sub folder it will travel that.

I we need to map the variable.

  

Click ok.

No I am taking the execute sql task to insert the file name into the table.

  

Now I am configuring the execute sql task.

Map the variable and click ok.

Remember here still I am setting the Retain same connection properties. By default values is false.

  

Let’s Run the package and see the result.

  

Completed successfully. Let’s see the time taken by it.

  

It took around 25 seconds.

Now I am setting the Retain same connection properties as true.

  

Now I am executing the package.

  

When we set the properties true it will take less time to complete the task because in previous case each time it opening the connection and closing the connection. But in second case it opens the connection once and after completing the task it will close this connection. If we are having the large transaction the it will be better to set the Retain same connection to true. For small transaction set is as false.