Friday, March 06, 2009

SQL Server 2008 data type to SSIS data type mapping

I'm reading: SQL Server 2008 data type to SSIS data type mappingTweet this !
While working with connections or source adapters, it's not that SSIS every time reads the source and maps to the precise SSIS data-type. For ex, especially while using a flat file connection, it happens many a times that the data-type of few of the columns are not as per the requirements. This frequently happens with sources like flat files and excel files.

Below is a mapping chart of SQL Server 2008 data types and SSIS data types which can come really handy to developers while working with tasks like source adapters, script tasks, derived columns and wherever a casting operation is being operated on a data-type.

SQL Server 2008 Data-type

SSIS Data-type

bigint

eight - byte signed integer [DT_I8]

binary

byte stream [DT_BYTES]

Bit

Boolean [DT_BOOL]

tinyint

single - byte unsigned integer [DT_UI1]

Datetime

database timestamp [DT_DBTIMESTAMP]

Decimal

numeric [DT_NUMERIC]

real

float [DT_R4]

Int

four - byte signed integer [DT_I4]

Image

image [DT_IMAGE]

Nvarchar or nchar

Unicode string [DT_WSTR]

Ntext

Unicode text stream [DT_NTEXT]

Numeric

numeric [DT_NUMERIC]

Smallint

two - byte signed integer [DT_I2]

Text

text stream [DT_TEXT]

Timestamp

byte stream [DT_BYTES]

Tinytint

single - byte unsigned integer [DT_UI1]

Uniqueidentifier

unique identifier [DT_GUID]

Varbinary

byte stream [DT_BYTES]

Varchar or char

string [DT_STR]

Xml

Unicode string [DT_WSTR]

Date

date-only [DT_DBDATE]

Time

time-only [DT_DBTIME]

1 comment:

Kostas said...

Thank you so much! This is very helpful!

Related Posts with Thumbnails