SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SQL to SSIS Data Type Mapping?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elomon
Starting Member

37 Posts

Posted - 02/26/2009 :  16:25:26  Show Profile  Reply with Quote
New to SSIS. I need to map a SQL data type (int) to an SSIS variable. I cannot find anywhere a list of mappings; all of the mappings from MSDN or elsewhere say int maps to DT_I4. When I make variables in SSIS, 'DT_I4' is not listed; instead it shows things like Double, DateTime, Object etc.

Does anyone know where I can find a solid mapping of SQL to SSIS types? Or, how I can figure out what 'DT_I4' means?

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 02/26/2009 :  17:05:20  Show Profile  Reply with Quote
'DT_I4' means 4-byte signed integer...when u say SSIS variable what exactly do you mean...where are u using this variable....because SSIS usually will show a list of the valid data types by itself.

Go to Top of Page

elomon
Starting Member

37 Posts

Posted - 02/26/2009 :  18:33:38  Show Profile  Reply with Quote
Right. In SSIS, create a new variable. Data types do not include '4 byte signed integer'. The dropdown for data types has datetime, object, string, int32, single, double etc.

How do i figure out what a '4 byte signed integer' means in this variable drop down?
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 02/26/2009 :  20:56:56  Show Profile  Reply with Quote
Check this for all SSIS data types.

http://msdn.microsoft.com/en-us/library/ms141036.aspx
Go to Top of Page

elomon
Starting Member

37 Posts

Posted - 02/27/2009 :  09:30:20  Show Profile  Reply with Quote
Unfortunately, that is exactly the page that is giving me fits.
How do I find a DT_I4 in a dropdown in SSIS? Here is what a datatype drop down looks like in SSIS:
http://img7.imageshack.us/my.php?image=ssisdatatypes.gif

The datatype selections don't have anything like what is listed in that page. How do I find mappings for the SSIS datatypes?
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 02/27/2009 :  10:50:08  Show Profile  Reply with Quote
Ok...Now I get what you mean. Check this. This should answer your question.

http://msdn.microsoft.com/en-us/library/ms345165(SQL.90).aspx
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 02/27/2009 :  10:51:44  Show Profile  Reply with Quote
Dont click this hyperlink.

Copy and paste the entire link in the browser including the (SQL.90).aspx

It dint get included in the hyperling for some reason
Go to Top of Page

elomon
Starting Member

37 Posts

Posted - 02/27/2009 :  10:58:16  Show Profile  Reply with Quote
Wonderful, amazing, thank you!
That is exactly what I needed.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 02/27/2009 :  11:02:03  Show Profile  Reply with Quote
Welcome
Go to Top of Page

SQLDevPlus
Starting Member

USA
1 Posts

Posted - 04/11/2011 :  02:09:49  Show Profile  Reply with Quote
To which SSIS Variable datatype should sql server Money Data Type be mapped?

sqldev
Go to Top of Page

gbritton
Aged Yak Warrior

662 Posts

Posted - 02/05/2014 :  10:43:56  Show Profile  Reply with Quote
quote:
Originally posted by vijayisonly

Ok...Now I get what you mean. Check this. This should answer your question.

http://msdn.microsoft.com/en-us/library/ms345165(SQL.90).aspx



That link is mostly helpful but it leads to more questions:

1. Can we use the methods described in our SSIS packages? How? (Examples)

2. Is there a mapping of SSIS variable types to Parameter types and Parameter types to SQL types?

e.g The system variable System:StartTime is of type DateTime (in the Variables tab).

In the Execute SQL task, in the Parameters tab, there is no type DATETIME in the drop-down. There is DATE, DBDATE, DBTIMESTAMP and some others. Note that they are not the same as the variable types (thanks, SSIS, for making life difficult!)

Say that you want to pass the variable System::StartTime to a query in an Execute SQL Task where the SQL data type is DATETIME. We have:

Variable          Variable Type  SQL type   Parameter tab type
----------------- ------------   --------   ------------------
System::StartTime DateTime       DATETIME   ???


By process of elimination, I discovered that the Parameter type in this case is DATE. That is confusing, since it actually does pass in the time as well.

Now, there are several SSIS variable types (11 in 2008-r2) and several Parameter types (27 in 2008-r2) and lots of SQL types. Is there a matrix somewhere that maps all three together?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000