SSIS configuration wizard gotcha

In all my applications, I use SQL Server configurations coupled with one indirect configuration. The latter brings me connection string for a connection manager which, in turn, enables my packages to use SQL Server configurations. This is what I call "hybrid" configuration.

A couple weeks ago, I ran into the following problem. I was trying to enter a connection string for a connection manager into my SSIS configuration table and I had this error message:

Clicking on the show technical details icon (rightmost icon at bottom of the error window), led me to this screen:

I highlighted the problem. Basically, the message tells me that the value to be inserted is too long. By default, SSIS creates a configuration table with NVARCHAR(255) columns. The problem I was facing is that when SSIS configures a connection manager connection string, it appends the application name to the connection string. So, my problem was that SSIS tried to insert a value that was longer than 255 characters into the configured value column. In order to get rid of the error, I had two choices:

1. Make the configured value column larger than 255 characters.
2. Remove the application name from the connection string

The application name is an optional parameter that is added to the connection string by SSIS connection manager. It looks like this:

"Application Name=SSIS-StgTemplate-{EE44D17C-AD8C-48E5-B8EB-8197272EE4C0}.\DEV2008.StgDb;"

It is useful when you want to trace SQL connections and distinguish among all active connections on your server. Since the application's name is, for me, superfluous information (I am not running concurrent ETL applications and most of the time, they run on a dedicated server), I decided to use the option #2 and my problem was resolved!