Wednesday, January 27, 2010

BUG: SSIS 2008 derived columns from User variables data type

I wanted to create a derived column from a user:variable in SSIS so that I could combine the input of a CSV into a SQL table along with a column name saying where the CSV came from.

Usually you initialise the user:variable before the package starts with a non relevant string, such as "1234" then updated it to the correct value once the process starts.

The problem is that when you use the Derived Coulmn Transformation Editor and add a Derived Column as SSIS fills in the Data Type automatically for you and wont let you change its data length. So if you initialise the user:variable with "1234" the new column will be defined with a Data type Unicode string [DT_WSTR] 4 - notice maximum length of 4!

So if you update the user:variable string to a length of 5 characters SSIS fails!

My work around is to make sure that you initialise the user:variable string with a value which has a length of the maximum possible string which you are likely to encouter.

Hopefully this will be resolved in future releases.

No comments: