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
Thanks for valuable information
ReplyDelete