SSIS And Its Data Types

If you’ve got some experience building Data Flows in your Integration Services packages, you probably already know that SSIS has its own representation of the different data types.  The names of these data types start with DT_, followed by the actual type such as BOOL for boolean and STR for string.

Some data types you use every day and others only once in a while.  So some you already know by heart and others you don’t.  A nice example of one that I certainly don’t need to look up anymore is the following cast as used in a Derived Column Transformation: (DT_STR, 100, 1252)YourField.  It converts YourField to a string field of length 100 using the ANSI – Latin 1 code page.

But the reason for this post are the other types, the ones not used daily.  Because each time that I need info on one of those, I find myself ploughing through several BOL pages before finding that page of which I know it exists but where, oh where?!

So, on the following Books Online page you can find a list of all SSIS data types and their definition: Integration Services Data Types

And at the bottom of that same page there’s an interesting table showing the mapping between the SSIS types and those of several RDBMS, including SQL Server of course.

Another interesting page is this one: Working with Data Types in the Data Flow

The bottom half of this page contains a mapping table between the SSIS data types and their corresponding managed type in .NET.  This is especially useful when you’re doing some custom development like a custom data flow component.  But you don’t need to take it that far, even when you’re just using the built-in Script Component task for some complex data conversions, you’ll find this useful.  After all, you’re also programming in a managed .NET language in that component.

Okay, that’s it for now, hopefully this will save you some time searching for those reference pages.  I already know that I’ll be coming back to this post once in a while