I have found often when teaching SSIS to others that it can be extremely confusing when you first encounter SSIS data types. At first glance they seem to be nothing like SQL Server data types you love and know. That's why I've provided below a conversion chart of SSIS data types to SQL Server data types. This information is readily available on MSDN but it always seems difficult to find. Hope this helps!
SSIS Data Type | SSIS Expression | SQL Server |
single-byte signed integer | (DT_I1) |
|
two-byte signed integer | (DT_I2) | smallint |
four-byte signed integer | (DT_I4) | int |
eight-byte signed integer | (DT_I8) | bigint |
single-byte unsigned integer | (DT_UI1) | tinyint |
two-byte unsigned integer | (DT_UI2) |
|
four-byte unsigned integer | (DT_UI4) |
|
eight-byte unsigned integer | (DT_UI8) |
|
float | (DT_R4) | real |
double-precision float | (DT_R8) | float |
string | (DT_STR, «length», «code_page») | char, varchar |
Unicode text stream | (DT_WSTR, «length») | nchar, nvarchar, sql_variant, xml |
date | (DT_DATE) | date |
Boolean | (DT_BOOL) | bit |
numeric | (DT_NUMERIC, «precision», «scale») | decimal, numeric |
decimal | (DT_DECIMAL, «scale») | decimal |
currency | (DT_CY) | smallmoney, money |
unique identifier | (DT_GUID) | uniqueidentifier |
byte stream | (DT_BYTES, «length») | binary, varbinary, timestamp |
database date | (DT_DBDATE) | date |
database time | (DT_DBTIME) |
|
database time with precision | (DT_DBTIME2, «scale») | time(p) |
database timestamp | (DT_DBTIMESTAMP) | datetime, smalldatetime |
database timestamp with precision | (DT_DBTIMESTAMP2, «scale») | datetime2 |
database timestamp with timezone | (DT_DBTIMESTAMPOFFSET, «scale») | datetimeoffset(p) |
file timestamp | (DT_FILETIME) |
|
image | (DT_IMAGE) | image |
text stream | (DT_TEXT, «code_page») | text |
Unicode string | (DT_NTEXT) | ntext |
