Introduction
Data types are very important factors when we work with SSIS. As we know very well about MS SQL Server data types but when we learning SSIS, sometimes we are confuse about data types of SSIS.
As the SSIS data types are different from the SQL Server data types because it not only supports the T-SQL but also supports others database systems such as Jet, DB2, and Oracle. With this it must supports Excel spreadsheets, comma-separated values (CSV) files, text files, directory services, and other sources.
Category of SSIS Data Types
We can divide the SSIS data types into following category
Numeric: Types that support numeric values formatted as currencies, decimals, and signed and unsigned integers. SSIS supports more numeric types than any other kind.
String: Types that support ANSI and Unicode character strings.
Date/Time: Types that support date values, time values, or both in various formats.
Binary: Types that support binary and image values.
Boolean: A type to handle Boolean values.
Identifier: A type to handle globally unique identifiers (GUIDs).
Comparing SSIS and SQL Server data types
The bellow chart compares the SQL Server and SSIS data type to understand it properly.
SSIS Data Types
|
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
|
References
Hope you like it.
Posted by: MR. JOYDEEP DAS
Nice post very helpful
ReplyDeletedbakings
I think its a very good post written to highlight an aspect of SSIS Postgresql Write .Thanks.
ReplyDelete