FOLLOW US

Thursday, December 8, 2011

SSIS datatypes VS SQL Server datatypes

Often it can be extremely confusing when you encounter SSIS data types.  At first glance they seem to be nothing like SQL Server data types.  That's why I wanted to write this blogpost,  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.  

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


Hope this helps!

No comments:

Post a Comment