SSIS Data Types

For all of its life, there have always been a lot of questions circulating around about data types in SSIS. What does an int in SQL map to in SSIS? What about a varchar? nvarchar? The questions could just go on and on. But, finally, we have an answer. In the all new SSISDB in 2012, there is a view built specifically to answer all of these questions. It’s called data_type_mapping, and exists as an actual table in the internal schema in tables. It’s a pretty small table, but it’s usefulness is far reaching. I’ve pasted the actual contents of the table here, for anyone who might not have SSIS 2012 or might not have the SSISDB set up.

mapping_id ssis_data_type sql_data_type
9 Boolean bigint
8 Boolean bit
10 Boolean int
11 Boolean smallint
12 Boolean tinyint
1 Byte bigint
13 Byte bit
25 Byte int
37 Byte smallint
44 Byte tinyint
22 DateTime datetime
21 DateTime datetime2
36 DateTime smalldatetime
55 Decimal decimal
58 Decimal float
57 Decimal numeric
56 Decimal real
23 Double decimal
24 Double float
33 Double numeric
35 Double real
2 Int16 bigint
14 Int16 bit
26 Int16 int
38 Int16 smallint
45 Int16 tinyint
3 Int32 bigint
15 Int32 bit
27 Int32 int
39 Int32 smallint
46 Int32 tinyint
4 Int64 bigint
16 Int64 bit
28 Int64 int
40 Int64 smallint
47 Int64 tinyint
5 SByte bigint
17 SByte bit
29 SByte int
41 SByte smallint
48 SByte tinyint
51 Single decimal
54 Single float
53 Single numeric
52 Single real
20 String char
32 String nchar
34 String nvarchar
6 UInt32 bigint
18 UInt32 bit
30 UInt32 int
42 UInt32 smallint
49 UInt32 tinyint
7 UInt64 bigint
19 UInt64 bit
31 UInt64 int
43 UInt64 smallint
50 UInt64 tinyint

 

Happy Developing!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s