Working with Data Types in the Data Flow

Working with Data Types in the Data Flow

 

Applies To: SQL Server 2016 Preview

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.

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 TypeSet MethodGet Method
DT_BOOLSetBooleanGetBoolean
DT_BYTESSetBytesGetBytes
DT_CYSetDecimalGetDecimal
DT_DATESetDateTimeGetDateTime
DT_DBDATESetDateGetDate
DT_DBTIMESetTimeGetTime
DT_DBTIME2SetTimeGetTime
DT_DBTIMESTAMPSetDateTimeGetDateTime
DT_DBTIMESTAMP2SetDateTimeGetDateTime
DT_DBTIMESTAMPOFFSETSetDateTimeOffsetGetDateTimeOffset
DT_DECIMALSetDecimalGetDecimal
DT_FILETIMESetDateTimeGetDateTime
DT_GUIDSetGuidGetGuid
DT_I1SetSByteGetSByte
DT_I2SetInt16GetInt16
DT_I4SetInt32GetInt32
DT_I8SetInt64GetInt64
DT_IMAGEAddBlobData or AddBlobDataGetBlobData
DT_NTEXTAddBlobData or AddBlobDataGetBlobData
DT_NULLSetNullThere is no Get method that is applicable to this data type.
DT_NUMERICSetDecimalGetDecimal
DT_R4SetSingleGetSingle
DT_R8SetDoubleGetDouble
DT_STRSetStringGetString
DT_TEXTAddBlobData or AddBlobDataGetBlobData
DT_UI1SetByteGetByte
DT_UI2SetUInt16GetUInt16
DT_UI4SetUInt32GetUInt32
DT_UI8SetUInt64GetUInt64
DT_WSTRSetStringGetString

Data Types to Use with the Set Methods

Set MethodData Type
AddBlobData or AddBlobDataDT_IMAGE, DT_NTEXT, or DT_TEXT
SetBooleanDT_BOOL
SetByteDT_UI1
SetBytesDT_BYTES
SetDateDT_DBDATE
SetDateTimeDT_DATE, DT_DBTIMESTAMP, DT_DBTIMESTAMP2, or DT_FILETIME
SetDateTimeOffsetDT_DBTIMESTAMPOFFSET
SetDecimalDT_CY, DT_DECIMAL, or DT_NUMERIC
SetDoubleDT_R8
SetGuidDT_GUID
SetInt16DT_I2
SetInt32DT_I4
SetInt64DT_I8
SetNullDT_NULL
SetSByteDT_I1
SetSingleDT_R4
SetStringDT_STR or DT_WSTR
SetTimeDT_DBTIME or DT_DBTIME2
SetUInt16DT_UI2
SetUInt32DT_UI4
SetUInt64DT_UI8

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.

System_CAPS_ICON_note.jpg Note


The mapping files in XML format that are installed by default to C:\Program Files\Microsoft SQL Server\130\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.

System_CAPS_ICON_caution.jpg Caution


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 TypeMaps to this Managed Data Type
DT_WSTRSystem.String
DT_BYTESArray of System.Byte
DT_DBTIMESTAMPSystem.DateTime
DT_DBTIMESTAMP2System.DateTime
DT_DBTIMESTAMPOFFSETSystem.DateTimeOffset
DT_DBDATESystem.DateTime
DT_DBTIMESystem.TimeSpan
DT_DBTIME2System.TimeSpan
DT_DATESystem.DateTime
DT_FILETIMESystem.DateTime
DT_NUMERICSystem.Decimal
DT_GUIDSystem.Guid
DT_I1System.SByte
DT_I2System.Int16
DT_I4System.Int32
DT_I8System.Int64
DT_BOOLSystem.Boolean
DT_R4System.Single
DT_R8System.Double
DT_UI1System.Byte
DT_UI2System.UInt16
DT_UI4System.UInt32
DT_UI8System.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.

System_CAPS_ICON_caution.jpg Caution


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 TypeMaps to this Integration Services Data Type
DT_DECIMALDT_NUMERIC
DT_CYDT_NUMERIC
DT_DATEDT_DBTIMESTAMP
DT_DBDATEDT_DBTIMESTAMP
DT_FILETIMEDT_DBTIMESTAMP
DT_DBTIMESTAMP2DT_DBTIMESTAMP
DT_DBTIMEDT_DBTIME2
DT_BOOLDT_I4
DT_TEXTDT_WSTR
DT_NTEXTDT_WSTR
DT_STRDT_WSTR
DT_IMAGEDT_BYTES
System_CAPS_ICON_note.jpg 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:



 Visit the Integration Services page on MSDN

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

BufferTypeToDataRecordType
DataRecordTypeToBufferType
ConvertBufferDataTypeToFitManaged
Integration Services Data Types

Community Additions

ADD
Show:
© 2016 Microsoft