THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Snack: Name Those Connections!

This blog has moved! You can find this content at the following new location:

Published Thursday, February 9, 2012 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Arlene said:

Thanks Andy!

February 9, 2012 7:19 AM

jamiet said:

Nice tip! I'd never thought of that.

Note that connection strings get logged automatically in SSIS2012 (assuming LOGGING_LEVEL=Basic or above) and connection strings contain the Application Name too - so you could possibly use this to correlate your log data with sp_who2 data.

February 9, 2012 7:27 AM

Jason Yousef said:

Good idea Andy...Thanks

February 9, 2012 7:33 AM

Andy Warren said:

Good stuff! Tagging connections is always a win for the DBA. For application level connections I commonly add the version number as well, helps to run down those users with deprecated versions.

February 9, 2012 8:42 AM

David Stein said:

That's great Andy. I've already started doing this.

February 9, 2012 8:57 AM

Samuel Vanga said:

Andy, I've been searching for this property ever since you mentioned it in a couple of your SSIS microtraining events with no luck.

Thanks for posting!

February 9, 2012 8:58 AM

Rafael Salas said:

Great tip Andy. I have used this in the past, but later found that many packages created via 'save as' or copy/paste carried connection names from the original package. So, a little of consistency and discipline is also needed.

February 9, 2012 9:21 AM

Robert Pearl said:

Definitely a good idea, and one that will pay off if you need to troubleshoot!  Thanks!

February 9, 2012 9:40 AM

Paul Hales said:

Good point Andy, Another reason for taking time to set this property is that attempting to create a configuration (connection string)for the connection manager may fail as the default connection manager connection string includes this Application Name property and if if you have a long package name plus the guid plus a long fully quallified database name, all are included by default in the connection string and it can overflow the default length of the "ConfiguredValue" column (nvarchar255) in SQL Server (assuming SQL configurations)...

Below is an example of the default connection string with a short DB name = DWAudit.  This string is 246 chars long. With a longer database name, attempting to create the configuration will fail the error reported by SSIS is an extremely useful..."Could not complete wizard actions, Cannot insert configuration information into the Configuration table".

Data;Initial Catalog=DWAudit;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Template-{2ECACFD4-B6CF-44FC-B40F-9182BCA57A92};

February 9, 2012 6:46 PM

zaim raza said:

Good tip andy.

February 12, 2012 6:19 AM

Randi said:


I notice that property in my config values and wondered how it was used.

February 17, 2012 7:34 AM

Toby Rogers said:

Great article.  Didn't realize we could do that and it would help with troubleshooting

July 19, 2012 9:16 AM

Cliff said:

We use alot of shared connections where I am so there are different packages using the same connection configuration. Unfortunately (at least in 2005/2008) the ApplicationName is not exposed as it's own property in the expressions.The connections are stored in a database table with a common configuration filter.

I doubt I can convince my company to go this route since there are so many packages currently, but if you have a similar setup, you could pull the connection into a variable, append the application name to the connection string, then assign to the Connection Manager via an expression.

August 27, 2012 12:08 PM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement