Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 20

Changing Data Types with the Data Conversion Transform

When working with data, you often 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 from Excel is set, which is brought in SSIS as 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 it into a varchar column, the execution may 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, first connect it to a source, then drag it onto the Data Flow designer and double-click it to open the Data Conversion Transformation Editor (shown in Figure 20-1). Here you check the columns you need to convert and use the Data Type drop-down menu to select the data type you want to convert to.

Figure 20-1

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 datatyped column. It was made this way to translate well into the .NET development world. The following table ...