Data type conversions using Derived Column component in SSIS

So if you’ve ever been unlucky enough to be in the position of creating a huge pile of staging package and need to do validation of data types in the DFT, you end up with the laborious task of typing out the conversions by hand for each column within the Derived Column component (you could use the Data Conversion component but it doesn’t let you redirect rows).

In such a scenario you would likely end up with a DFT looking something like:

You may notice I’ve used Jamie Thomson’s naming conventions ( http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx ).

The transformation for the Derived Column component will end up looking like this:

The real problem comes when you have 50+ column wide tables and your fingers are fat there is a risk of getting the data types wrong and inadvertently truncating. I figured there had to be a better way. And here is a step towards it:

The script creates a table variable that contains all the SSIS data types and each of their corresponding t-sql data types, then goes off and looks at the system view for your destination table to get the datatypes for each column. Pretty straightforward really. It actually outputs two columns for the Expression, one with the actual column names if these happen to have been specified in your source (ie source from table or flat file with column headers) and the other with [Column 0], [Column 1] etc

I’m sure it could be tidier, but hopefully it saves you some time in trouble shooting a column that is missing those crucial last few decimal places.

Providing this SkyDrive thing works you should be able to find the script here