SSIS Performance Tuning the For Each Loop. Retain Same Connection.

With the Retain Same Connection property I was recently able to more than double the performance of my SSIS package for a client. The package was looping over hundreds of thousands of files and logging the file names into a table to be later processed.

Retain Same Connection is a property setting found on connection managers. By default this property is set to false which means that each time the connection manager is used the connection is opened and subsequently closed. However in a situation like mine this can significantly degrade overall performance as the package has to open and close that connection hundreds of thousands of times. In this blog I’m going to set up a very basic example and walk through setting up this connection manager.

Here I have set up two connection managers. Both connection managers point to the same database. It’s important to note that if you are using Project Level Connection managers in SQL 2012 that setting this property inside any one package will persist across all packages. Therefore I create two connection managers.

image

Inside the package I am simply using a for each loop task to loop through a list of files in a directory and then I load the file names into a table using an Execute SQL task. For demo purposes I have two examples in one package.

image

  1. The FELC on the left takes 30 Seconds to run.
  2. The FELC on the right takes 11 Seconds to run.

Let’s now discuss how and where we can set this property.

Right click on your connection manager found in the connection managers pain inside the package and select Properties.

image

Inside the properties window find the property “RetainSameConnection” and set the value to “True”. Now the connection will remain open for the duration of the package.

image

As always thanks for looking!

Advertisements

4 thoughts on “SSIS Performance Tuning the For Each Loop. Retain Same Connection.

  1. Daniel Polito February 24, 2017 / 11:06 pm

    Are the properties of the project connection manager scoped to the package or to the project? Does changing the retain same connection property in one package affect all packages in the same project?

    • Mitchell Pearson February 25, 2017 / 1:41 am

      Hey Daniel,

      From my recollection you are correct. If you change the property on a connection manager that is scoped at the project level then the change takes place in all packages that use that connection manager. My preference here is to have two project connection managers. One that has retain same connection set to true and one that is set to false. That said, the default behavior is set to false but I have never found a great reason on why this should be turned off. I would imagine it could somehow adversely affect the network if connections were left open for extended amounts of time, but not sure to what extent.

      Thanks for checking out my blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s