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

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

http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations

Hope you like it.

Posted by: MR. JOYDEEP DAS

Data Types

As we all know the selection of proper data types is very important for a SQL Server Table objects. Without proper data types the performance is going down and it is un manageable for SQL developer.

In this article I am trying to gather and provide a chart that contains most useful data types used in Microsoft SQL Server Version 2000/2005/2008.

Datatype	Min	Max	Storage	Type	Notes
Bigint	-2^63	2^63-1	8 bytes	Exact numeric
Int	-2,147,483,648	2,147,483,647	4 bytes	Exact numeric
Smallint	-32,768	32,767	2 bytes	Exact numeric
Tinyint	0	255	1 bytes	Exact numeric
Bit	0	1	1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...	Exact numeric
Decimal	-10^38+1	10^38–1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes	Exact numeric	Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric	no
Money	-2^63 / 10000	2^63-1 / 10000	8 bytes	Exact numeric
Smallmoney	-214,748.3648	214,748.3647	4 bytes	Exact numeric
Float	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53	Approximate numeric	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes	Approximate numeric	Precision is fixed to 7.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes	Date and time	If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59		Date and time
Date  (SQL2008)	0001-01-01	9999-12-31		Date and time
Time (SQL2008)	00:00:00.0000000	23:59:59.9999999		Date and time	Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2 (SQL2008)	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes	Date and time	Combines the date data type and the time data type into one. The precision logic is the same as for the time data type.
Datetimeoffset (SQL2008)	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00	Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes	Date and time	Is a datetime2 data type with the UTC offset appended.
Char	0 chars	8000 chars	Defined width	Character string	Fixed width
Varchar	0 chars	8000 chars	2 bytes + number of chars	Character string	Variable width
Varchar(max) (SQL2005/2008)	0 chars	2^31 chars	2 bytes + number of chars	Character string	Variable width
Text	0 chars	2,147,483,647 chars	4 bytes + number of chars	Character string	Variable width
Nchar	0 chars	4000 chars	Defined width x 2	Unicode character string	Fixed width
Nvarchar	0 chars	4000 chars		Unicode character string	Variable width
Nvarchar(max) (SQL2005/2008)	0 chars	2^30 chars		Unicode character string	Variable width
Ntext	0 chars	1,073,741,823 chars		Unicode character string	Variable width
Binary	0 bytes	8000 bytes		Binary string	Fixed width
Varbinary	0 bytes	8000 bytes		Binary string	Variable width
Varbinary(max) (SQL2005/2008)	0 bytes	2^31 bytes		Binary string	Variable width
Image	0 bytes	2,147,483,647 bytes		Binary string	Variable width
Sql_variant				Other	Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
Timestamp				Other	Stores a database-wide unique number that gets updated every time a row gets updated.
Uniqueidentifier				Other	Stores a globally unique identifier (GUID).
Xml (SQL2005/2008)				Other	Stores XML data. You can store xml instances in a column or a variable.
Cursor				Other	A reference to a cursor.
Table				Other	Stores a result set for later processing.

Hope you like it.

Posted by: MR. JOYDEEP DAS