Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 16. Changing Data Types with the Data Conversion Transform

Often when working with data, you have various reasons why you may need to make changes to a column's data type. For example, SQL Server Integration Services (SSIS) supports Excel data as a source, but it may not support the data the way you intend by default. By default the general data type is set, which is a Unicode data type. In SQL Server, Unicode translates to an nvarchar, which is most likely not what you want because it requires twice the space and may be slower. If you have a Unicode data type in SSIS and you try to insert into a varchar column, it will potentially fail.

The Data Conversion Transform performs the T-SQL equivalent of the CONVERT or CAST functions on a selected column. To configure this transform, drag it onto the Data Flow designer and double-click it to open the Data Conversion Transformation Editor (shown in Figure 16-1). Here you check the columns you need to convert and use the Operation drop-down box select the data type you want to convert to.

Something that can be frustrating with SSIS is how it deals with SQL Server data types. For example, a varchar maps in SSIS to a string data typed column. It was made this way to translate well into the .NET development world. The following table shows how the data types translate from a SQL Server data type to an SSIS data type.

SQL Server Data Type	SSIS Data Type
Bigint	Eight-byte signed integer [DT_I8]
Binary	Byte stream [DT_BYTES]
bit	Boolean [DT_BOOL] ...