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:

DFT

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:

DER

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

Advertisements

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on March 17, 2010, in SSIS. Bookmark the permalink. Leave a comment.

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: