O'Reilly logo

Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services by Mike Davis, Devin Knight, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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] ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required