Thursday 3 December 2015

SSIS – Data Type Vs SQL Server Data type

Introduction
I often find that developers are little bit confused when working with SSIS data type. As most of the developer came from Microsoft SQL Server background and they are well familiars with SQL Server data type. But SSIS provide different type of data type and the developers mostly from SQL background are confused with it.

This article is related to the Data type of SSIS. Hope it will provide some valuable information.

Why the Data Type of SSIS is different
 I faced this question frequently that SSIS is a tool of Microsoft SQL Server so why the data type of the SSIS is different the Microsoft SQL Server data type.

The answer is quite easy and if you understand SSIS, you can find that answer. 

The SSIS is not only dealing with Microsoft SQL Server. It can be used with almost all the Data source and Destination. For example Raw File, Flat File, Excel, DB2, Oracle, SQL Server etc. So the SSIS need a common data type to support all type of data source and destination and that’s the reason the Data type of SSIS is little different with MS SQL Server data type.

Comparing SSIS Data Type With SQL Server Data type
Here I am going to mention a chart that can help you to identify the proper data type of SSIS in compare with MS SQL Server.

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

1 comment: