Connection Manager

What is the RetainSameConnection Property of OLEDB Connection in SSIS?

Aalam Rangi4 Comments

I recently wrote about How to Use Temp Table in SSIS. One of the requirements to successfully reuse a temporary table across multiple tasks in SSIS is to set the RetainSameConnection property of the OLEDB Connection to TRUE. In this post, I will discuss the property and also use a Profiler Trace to find out its behavior.

The RetainSameConnection Property

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. I believe the idea behind this is to not block a connection to a server unnecessarily and release it until it is needed again. And it makes sense too because some packages can run for an extended duration and may not need to be connected to an OLEDB server all the time. For example, an OLEDB in not required to be open while parsing text files, sending emails, ETL operations not involving the OLEDB server in question etc. Releasing connections unless really required can be certainly helpful on busy servers because SQL Server needs some memory for each open connection.

On the other hand, some scenarios require a persistent connection e.g. temporary table reuse across multiple tasks. We can set the property value to TRUE and then it will open just one OLEDB connection with a server and keep it alive until the end of the package execution. The property can be set via the Properties window for the OLEDB Connection Manager.

aalamrangi.wordpress.com-SSISRetainSameConnection2

The Temporary Table Scenario

Local temporary tables (with a # in front of their name) in SQL Server are scoped to a session. SQL Server drops them when the session is closed. This means, local temporary tables created in one session are not available in another session. In SSIS, with the RetainSameConnection set to FALSE (the default), a new session is opened for each task. Therefore, temporary tables created by a task are not available to another task.

Demo

I have a demo package with two Execute SQL Tasks and one OLEDB Connection Manager. The Execute SQL Tasks have a simple SELECT statement and they both use the same connection manager.

aalamrangi.wordpress.com-SSISRetainSameConnection1

I have a Profiler Trace to monitor the number of connections created by the SSIS package.

The first execution of the package is with the RetainSameConnection set to the default value of FALSE. The trace captures two pairs of login/logout events, one for each task. The second execution is with the property value set to TRUE. This time, the trace captures only one pair of login/logout events.

aalamrangi.wordpress.com-SSISRetainSameConnection3

Conclusion

In most cases, the default value of RetainSameConnection=FALSE will be suitable. A developer should make a decision to enable it when the package tasks really need a persistent connection. In addition to the temporary table reuse, a TRUE value for this property can also be useful in managing transactions and reducing the number of recurring connection requests to a server.

Using a Meaningful Application Name in the OLEDB Connection String

Aalam Rangi4 Comments

The default connection string for a OLEDB connection looks something like this –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoOLEDBPackage-{9758C970-4AAC-449E-8390-FA7EAC5CD52A}(local).ReportServer;
 

Notice the Application Name parameter in bold font. It has the name of the package, a unique Id for the connection, the SQL server name and the database name. This application name will be exported out to the config file too. Also, this is what you will see in the Program Name column of SP_WHO2 results when the package is running.

When the developers create the packages on a development server, the connection string includes the development server name. At the time of migrating the package and its config file to the QA and Production environment, the developers usually change the Data Source and Initial Catalog in the connection string but leave the Application Name as it is. Anyone monitoring the Production server with a SP_WHO2 would see a package running with a funny long name including GUID and the development server name. It is certainly not user-friendly.

This can be fixed easily with any one of the following methods.

Method 1 – Properties Window

Right click the OLEDB connection and select Properties (or select the connection and press F4). The Properties window will show up. Edit the Connection String property to change the Application Name parameter of the connection in this window.

Method 2 – Config File

Edit the Connection String property to change the Application Name parameter of the connection in the config file. As we know, the values in the config file override the design time values when the package is running. Although I prefer making the initial change in the package itself before creating the config file.

Method 3 – The Connection Manager Wizard

  1. Double click the OLEDB Connection.
  2. Click on the All Tab.
  3. Edit the Application Name.

SSISOLEDBConnectionStringApplicationName

Now the connection string would look like the following, which is a lot cleaner than the default string –

Data Source=(local);Initial Catalog=ReportServer;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-DemoPackage;
 

I prefer using a _ convention based on the target environment e.g. SSIS-DemoPackage_TEST, SSIS-DemoPackage_QA or SSIS-DemoPackage_PROD in the different config files. You can plan to put in other information to make it more meaningful in your context.

I’d love to hear your thoughts on this!