Export (0) Print
Expand All
1 out of 6 rated this helpful - Rate this topic

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.

ms345165.note(en-US,SQL.90).gifNote:
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.

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.

ms345165.Caution(en-US,SQL.90).gifCaution:
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

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.

ms345165.Caution(en-US,SQL.90).gifCaution:
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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.