Mapping Data Types in the Data Flow

New: 5 December 2005

While moving data from sources through transformations to destinations, a data flow component must sometimes convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. In addition, a component must sometimes convert one Integration Services data type to another before that type can be converted to a managed type.

Note

The mapping files in XML format that are installed by default to C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles are not related to the data type mapping discussed in this topic. These files map data types from one database version or system to another (for example, from SQL Server 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle), and are used only by the SQL Server Import and Export Wizard. For more information on these mapping files, see Creating Packages Using the SQL Server Import and Export Wizard.

Mapping between Integration Services and Managed Data Types

Sometimes a data flow component must convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. The following table lists the conversions that are currently performed by the BufferTypeToDataRecordType and the DataRecordTypeToBufferType methods of the PipelineComponent class.

Warning

Developers should use these methods of the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Integration Services Data Type	Managed Data Type
DT_WSTR	System.String
DT_BYTES	Array of System.Byte
DT_DBTIMESTAMP	System.DateTime
DT_NUMERIC	System.Decimal
DT_GUID	System.Guid
DT_I1	System.Byte
DT_I2	System.Int16
DT_I4	System.Int32
DT_I8	System.Int64
DT_BOOL	System.Boolean
DT_R4	System.Single
DT_R8	System.Double
DT_UI1	System.Byte
DT_UI2	System.UInt16
DT_UI4	System.UInt32
DT_UI8	System.UInt64

Converting Integration Services Data Types to Fit Managed Data Types

Sometimes a data flow component must also convert one Integration Services data type to another before that type can be converted to a managed type. The following table lists the conversions that are currently performed by the ConvertBufferDataTypeToFitManaged method of the PipelineComponent class.

Warning

Developers should use these methods of the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Original Data Type	Converted Data Type
DT_DECIMAL	DT_NUMERIC
DT_DATE	DT_DBTIMESTAMP
DT_BOOL	DT_I4
DT_TEXT	DT_WSTR
DT_STR	DT_WSTR
DT_IMAGE	DT_BYTES

See Also

Reference

BufferTypeToDataRecordType
DataRecordTypeToBufferType
ConvertBufferDataTypeToFitManaged

Other Resources

Integration Services Data Types

Help and Information

Getting SQL Server 2005 Assistance