This blog post here on BI Developer Network contains a nice list on data conversion between SSIS and SQL server, something that gets my blood pressure going due to SSIS’s ‘check your metadata’ error message. Good cheat sheet that list the data types.
|
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 |
Credit goes to their user, DevinKnight.