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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: