Mapping of SQL Server data types to Integration Services Data Type

Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?

For example, Integration Services has "DT_DATE", "DT_DBDATE", "DT_DBTIME" and "DT_DBTIMESTAMP". So far, if I have a SQL Server datetime column, the only Integration Services type I have been able to use is "DT_DBTIMESTAMP". There must be a way to map the datetime type to "DT_DATE", "DT_DBDATE" and "DT_DBTIME", but there no easy to use reference for this.

Please post a link to the resources if you know of one.

Thanks.We are just in the process of refining such a topic for the around-RTM Web refresh of Books Online. Copying and pasting HTML out of BOL is usually a disaster, but I'll give it a try, below. It will look more user-friendly when it appears in BOL! This topic has not been fully edited and tech reviewed - use at your own risk.

-Doug

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

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. Other Integration Services data types not listed here cannot be converted to managed types.

Caution: Developers should use these methods of the 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 any 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.

Caution: Developers should use these methods of the 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 any 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

|||This is a good start, thanks. I'll be mindful of the risks in using it.

Ken|||Let me clarify in particular that the sentence, "Other Integration Services data types not listed here cannot be converted to managed types." (already rewritten since that build of BOL) means "...by using these methods." That is, the API methods mentioned in that paragraph.|||

If you interested in mapping of tinyints have a look at my post

http://www.sqljunkies.com/WebLog/simons/archive/2006/02/24/tinyint_in_SSIS.aspx