Share via


Working with Data Types in the Data Flow

When developing a custom data flow component in Integration Services, you work constantly with data types, copying data into and out of data flow buffers and transforming values. The information in this topic helps you to choose the correct Integration Services data types, and to use the correct methods when working with them.

Inserting Data into the Data Flow

The PipelineBuffer class provides a series of Set methods for copying data into buffer columns, and a corresponding series of Get methods for retrieving data from buffer columns. The following tables show you which method to use with each Integration Services data type.

Set Methods to use with Data Types

The following table lists the data type in the first column, and then lists the corresponding Set and Get methods.

Data Type

Set Method

Get Method

DT_BOOL

SetBoolean

GetBoolean

DT_BYTES

SetBytes

GetBytes

DT_CY

SetDecimal

GetDecimal

DT_DATE

SetDateTime

GetDateTime

DT_DBDATE

SetDate

GetDate

DT_DBTIME

SetTime

GetTime

DT_DBTIME2

SetTime

GetTime

DT_DBTIMESTAMP

SetDateTime

GetDateTime

DT_DBTIMESTAMP2

SetDateTime

GetDateTime

DT_DBTIMESTAMPOFFSET

SetDateTimeOffset

GetDateTimeOffset

DT_DECIMAL

SetDecimal

GetDecimal

DT_FILETIME

SetDateTime

GetDateTime

DT_GUID

SetGuid

GetGuid

DT_I1

SetSByte

GetSByte

DT_I2

SetInt16

GetInt16

DT_I4

SetInt32

GetInt32

DT_I8

SetInt64

GetInt64

DT_IMAGE

AddBlobData or AddBlobData

GetBlobData

DT_NTEXT

AddBlobData or AddBlobData

GetBlobData

DT_NULL

SetNull

There is no Get method that is applicable to this data type.

DT_NUMERIC

SetDecimal

GetDecimal

DT_R4

SetSingle

GetSingle

DT_R8

SetDouble

GetDouble

DT_STR

SetString

GetString

DT_TEXT

AddBlobData or AddBlobData

GetBlobData

DT_UI1

SetByte

GetByte

DT_UI2

SetUInt16

GetUInt16

DT_UI4

SetUInt32

GetUInt32

DT_UI8

SetUInt64

GetUInt64

DT_WSTR

SetString

GetString

Data Types to Use with the Set Methods

Set Method

Data Type

AddBlobData or AddBlobData

DT_IMAGE, DT_NTEXT, or DT_TEXT

SetBoolean

DT_BOOL

SetByte

DT_UI1

SetBytes

DT_BYTES

SetDate

DT_DBDATE

SetDateTime

DT_DATE, DT_DBTIMESTAMP, DT_DBTIMESTAMP2, or DT_FILETIME

SetDateTimeOffset

DT_DBTIMESTAMPOFFSET

SetDecimal

DT_CY, DT_DECIMAL, or DT_NUMERIC

SetDouble

DT_R8

SetGuid

DT_GUID

SetInt16

DT_I2

SetInt32

DT_I4

SetInt64

DT_I8

SetNull

DT_NULL

SetSByte

DT_I1

SetSingle

DT_R4

SetString

DT_STR or DT_WSTR

SetTime

DT_DBTIME or DT_DBTIME2

SetUInt16

DT_UI2

SetUInt32

DT_UI4

SetUInt64

DT_UI8

Mapping Data Types in the Data Flow

While moving data from sources through transformations to destinations, a data flow component must sometimes convert data types between the SQL Server Integration Services 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\110\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 to Oracle), and are used only by the SQL Server Import and Export Wizard. For more information on these mapping files, see SQL Server Import and Export Wizard.

Mapping between Integration Services and Managed Data Types

The PipelineComponent.BufferTypeToDataRecordType and the PipelineComponent.DataRecordTypeToBufferType methods map Integration Services data types to managed data types.

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.

The following table lists how the BufferTypeToDataRecordType and the DataRecordTypeToBufferType methods map various Integration Services data types to managed data types.

Integration Services Data Type

Maps to this Managed Data Type

DT_WSTR

System.String

DT_BYTES

Array of System.Byte

DT_DBTIMESTAMP

System.DateTime

DT_DBTIMESTAMP2

System.DateTime

DT_DBTIMESTAMPOFFSET

System.DateTimeOffset

DT_DBDATE

System.DateTime

DT_DBTIME

System.TimeSpan

DT_DBTIME2

System.TimeSpan

DT_DATE

System.DateTime

DT_FILETIME

System.DateTime

DT_NUMERIC

System.Decimal

DT_GUID

System.Guid

DT_I1

System.SByte

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

Mapping 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 PipelineComponent.ConvertBufferDataTypeToFitManaged method class maps Integration Services data types to other Integration Services data types that can then be mapped to managed data types by using the PipelineComponent.BufferTypeToDataRecordType method.

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.

The following table lists how the ConvertBufferDataTypeToFitManaged method maps Integration Services data types to other Integration Services data types.

Original Integration Services Data Type

Maps to this Integration Services Data Type

DT_DECIMAL

DT_NUMERIC

DT_CY

DT_NUMERIC

DT_DATE

DT_DBTIMESTAMP

DT_DBDATE

DT_DBTIMESTAMP

DT_FILETIME

DT_DBTIMESTAMP

DT_DBTIMESTAMP2

DT_DBTIMESTAMP

DT_DBTIME

DT_DBTIME2

DT_BOOL

DT_I4

DT_TEXT

DT_WSTR

DT_NTEXT

DT_WSTR

DT_STR

DT_WSTR

DT_IMAGE

DT_BYTES

Note

The ConvertBufferDataTypeToFitManaged method does not return a value for the DT_DBTIMESTAMPOFFSET data type, and a UnsupportedBufferDataTypeException occurs. You must convert the DT_DBTIMESTAMPOFFSET data type to one of the Integration Services date/time data types that can be mapped to a managed data type. For a list of Integration Services date/time data types that can be mapped to a managed data types, see the table in the previous section, "Mapping between Integration Services and Managed Data Types." For information about converting data types, see Integration Services Data Types.

Integration Services icon (small) 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:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Reference

BufferTypeToDataRecordType

DataRecordTypeToBufferType

ConvertBufferDataTypeToFitManaged

Concepts

Integration Services Data Types