SSIS to SQL Server Data Type Translations

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s