Thursday, 18 April 2013

SSIS Data Types

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

1 comment: