Relationship between SSIS and MS SQL Data Type
Introduction
As all the MS SQL Developer is familiar about the data type of MS SQL Server but they have some confusion related to SSIS data type.
In this article I am trying to build a relation between MS SQL Data type and SSIS data type that can helps junior SSIS developers to understand.
Relationship between SSIS and MS SQL Data Type
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.
What is USER DEFINE Data Type and It’s Implementation
User define data type are based on system data type of MS SQL Server. It is created when several table object use the same data in terms of data type, length and null ability.
For example if we create the user define data type like, postal code. The postal code data type contains same length and non null character data type. The postal code data type can be used in several table objects where it is needed.
When you create the user define data types you must supply the parameters like.
1. Name of the user define data type.
2. System data type on which the user defines data type based on.
3. Null ability, means whether the data type supports Null value or Not.
If we create the user define data types in model database, when we create the new data base on the same server the user define data type automatically exists in the new database.
The syntax of creating user define data types are mentioned bellow
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
@typename
It is the name of user defines data type. It must be unique in database.
@phystype
It contains the MS SQL server data types.
@nulltype
It define that the user define data type may contain NULL value or not. It contains NULL, NOT NULL or NONNULL
@owner
It specifies the owner or creator of the data type.
It returns the 0 for success or 1 for failure.
Example of creating user defines data types:
-- Creating User define data type
EXEC sp_addtype @typename='Postal_Code',
@phystype='Varchar(7)',
@nulltype='NOT NULL'
GO
-- Using User define data type
CREATE TABLE emp_address
(Emp_name VARCHAR(50) NOT NULL,
Postal_code Postal_code)
GO
I think this small posting is quite informative and thanking for giving time.
