Wednesday, March 14, 2012

SSIS to SQL Server Data Type Translations

While implementing SSIS package i have faced conversion problems SSIS data types. So here is bellow chart SSIS data types to SQL Server 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

Reference Links
http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations
http://www.sqlservercentral.com/blogs/dknight/2010/12/22/ssis-to-sql-server-data-type-translations/