jump to navigation

SSIS Package Configurations August 5, 2009

Posted by willem42195 in Business Intelligence.
Tags: ,
add a comment

A quick post about two (minor) issues I ran into when defining package configurations in SQL Server. Note: I’m using SSIS 2008 but I guess they equally apply to SSIS 2005.

I store all my configurations in SQL Server, which by default suggests a table called dbo.SSIS Configurations. Working on multiple projects for different clients, they all have their own preference on how to call this table and which schema to put it in. Since I have developed a template (including auditing, error logging, performance monitoring, etc.) that I want to be universally deployable, I created a synonym dbo.SSIS Configurations pointing to wherever the physical table is. This seems to be working well, but not when adding a new configuration or modifying an existing one! Suddenly BIDS starts complaining that the table is not in the format it expects it to be.

config error

So there is no other option than to select the physical table for adding a configuration. Aftwerwards, you can edit the package in XML to use the synonym and it will work. If you want to change the configuration, you either have to delete and recreate it or change the XML directly. Because this is a template, this approach is acceptable. The template itself doesn’t change very often. And when using the template in a development project, the location of the physical table is known (and fixed) and there is no need for the synonym anymore.

As a sidenote: when copying the template package, BIDS 2008 automatically changes the ID of the package. Unfortunately this is still not true for the indiviudal components in the package.

The second issue comes forth from a long connection string. When trying to add a a configuration for a ConnectionString, I got the following very clear error message: 

connection string

The actual problem is that the ConfiguredValue is longer than the 255 characters that are foreseen in the table. This is (partially) caused by the fact that BIDS adds a very long Application Name attribute to the ConnectionString. I solved this issue by first saving the configuration to an XML file. I copied the ConnectionString and manually created a record in the SSISConfigurations table with a shortened Application Name. Then in BIDS  I added the configuration (from SQL Server) by selecting the ConfigurationFilter I just created and choosing ‘Reuse Existing’.

Probably making the size of the column larger would have worked as well although I’m not 100% sure there won’t be any side effects. Since in my case, shortening the ConnectionString was easy to do, I preferred to stay on the safe side.