Relationship between SSIS and MS SQL Data Type

Introduction

As all the MS SQL Developer is familiar about the data type of MS SQL Server but they have some confusion related to SSIS data type.

In this article I am trying to build a relation between MS SQL Data type and SSIS data type that can helps junior SSIS developers to understand.

Relationship between SSIS and MS SQL Data Type

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 you like it.

Posted by: MR. JOYDEEP DAS