Data Conversion Transformation

The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. For example, a package can extract data from multiple sources, and then use this transformation to convert columns to the data type required by the destination data store. You can apply multiple conversions to a single input column.

You can configure the Data Conversion transformation in the following ways:

• Specify the columns that contain the data to convert and the types of data conversions to perform.

• Specify whether the transformation output columns use the quicker, but locale-insensitive, fast parsing routines that Microsoft SQL Server Integration Services provides or the standard locale-sensitive parsing routines.

Using this transformation, a package can perform the following types of data conversions:

• Change the data type. For more information, see Integration Services Data Types.

  Note

  If you are converting data to a date or a datetime data type, the date in the output column is in the ISO format, although the locale preference may specify a different format.

• Set the column length of string data and the precision and scale on numeric data. For more information, see Precision, Scale, and Length (Transact-SQL).

• Specify a code page. For more information, see Comparing String Data.

  Note

  When copying between columns with a string data type, the two columns must use the same code page.

If the length of an output column of string data is shorter than the length of its corresponding input column, the output data is truncated. For more information, see Handling Errors in the Data Flow.

This transformation has one input, one output, and one error output.

Configuring the Data Conversion Transformation

You can set properties through the SSIS Designer or programmatically.

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Data Conversion Transformation Editor dialog box, see Data Conversion Transformation Editor.

For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

• Common Properties

• Transformation Custom Properties

For more information about how to set properties, click one of the following topics:

• How to: Convert Data to a Different Data Type by Using the Data Conversion Transformation

• How to: Set the Properties of a Data Flow Component

External Resources

Blog entry, Performance Comparison between Data Type Conversion Techniques in SSIS 2008, on blogs.msdn.com.

Stay Up to Date with Integration Services For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet: Visit the Integration Services page on MSDN Visit the Integration Services page on TechNet For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

Fast Parse

Designing Package Data Flow

Integration Services Transformations