THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Snack: Name Those Connections!

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!

:{>

Published Thursday, February 09, 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

Comments

 

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 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;

February 9, 2012 6:46 PM
 

zaim raza said:

Good tip andy.

February 12, 2012 6:19 AM
 

Randi said:

Thanks,

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

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement