When creating Connection Managers in SSIS, take a moment to click the “All” page and set a value in the Application Name property. In SSIS 2008+, a default value is applied to this property. It’s useful but it contains a GUID. Yuck. I prefer to enter a more concise identifier like the one shown here:
“Why should I enter this information, Andy?” I’m glad you asked! When troubleshooting performance or locking / blocking issues, DBAs start with a query to ascertain what is currently executing calls to the SQL Server instance. As shown below, sp_who2 is one way to obtain a peek into the inner workings of SQL Server. Note the column labeled “Program Name” displays the value we configured into the Application Name property of the SSIS Connection Manager. This gives the DBA a fighting chance at isolating SSIS packages from other applications and jobs executing.
I use the following format:
[SSIS].<Package Name>.<Connection Manage Name>
The format you choose isn’t all that important. Being consistent is very important.
Happy Integrating!
:{>
Thanks Andy!
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.
Good idea Andy…Thanks
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.
That’s great Andy. I’ve already started doing this.
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!
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.
Definitely a good idea, and one that will pay off if you need to troubleshoot! Thanks!
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 Source=drdasql203.dev.ebc.hosts.network;Initial Catalog=DWAudit;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Template-{2ECACFD4-B6CF-44FC-B40F-9182BCA57A92}drdasql203.dev.ebc.hosts.network.DWAudit;
Good tip andy.
Thanks,
I notice that property in my config values and wondered how it was used.
Great article. Didn’t realize we could do that and it would help with troubleshooting
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.
Thank you, Good information.