Edit

Working with Data Types in the Data Flow

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

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\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 BufferTypeToDataRecordType and the DataRecordTypeToBufferType methods map Integration Services data types to managed data types.

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 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 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 BufferTypeToDataRecordType method.

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 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.

See Also

BufferTypeToDataRecordType
DataRecordTypeToBufferType
ConvertBufferDataTypeToFitManaged
Integration Services Data Types