When creating packages in SSIS, there is usually a need to map source data types to destination data types. This is where SSIS to SQL Server data type conversion chart could prove very useful.
When I first started using the SSIS tool, I frequently encountered data conversion issues when moving data to and from SQL Server. However, I was able to improve mastery and increase productivity by creating an SSIS to SQL Server data type conversion chart.
Today, I will be sharing this conversion chart with you.
| SSIS Data type | SQL Server Data Type |
| Boolean [DT_BOOL] | bit |
| byte stream [DT_BYTES] | binary, varbinary, timestamp |
| currency [DT_CY] | smallmoney, money |
| database date [DT_DBDATE] | date |
| database time [DT_DBTIME] | |
| database time with precision [DT_DBTIME2] | time[p] |
| database timestamp [DT_DBTIMESTAMP] | datetime, smalldatetime |
| database timestamp with precision [DT_DBTIMESTAMP2] | datetime2 |
| database timestamp with timezone [DT_DBTIMESTAMPOFFSET] | datetimeoffset[p] |
| date [DT_DATE] | date |
| decimal [DT_DECIMAL] | decimal |
| double-precision float [DT_R8] | float |
| eight-byte signed integer [DT_I8] | bigint |
| eight-byte unsigned integer [DT_I8] | bigint |
| file timestamp [DT_FILETIME] | |
| float [DT_R4] | real |
| four-byte signed integer [DT_I4] | int |
| four-byte unsigned integer [DT_I4] | int |
| image [DT_IMAGE] | image |
| numeric [DT_NUMERIC] | decimal, numeric |
| single-byte signed integer [DT_I1] | tinyint |
| single-byte unsigned integer [DT_I1] | tinyint |
| string [DT_STR] | char, varchar |
| text stream [DT_TEXT] | text |
| two-byte signed integer [DT_I2] | smallint |
| two-byte unsigned integer [DT_I2] | smallint |
| Unicode string [DT_NTEXT] | ntext |
| Unicode text stream [DT_WSTR] | nchar, nvarchar, sql_variant, xml |
| unique identifier [DT_GUID] | uniqueidentifier |
You can get more information about similar data type conversions by reading the Microsoft SQL Server documentation. The documentation also provides conversions for other databases such as Oracle and DB2,
If you’re a beginner, I recommend you bookmark this page for future reference until you master these SSIS to SQL Server data type conversions.
Did you like this article? Learn more about SSIS and SQL.
Happy Analyzing!