Author |
Topic |
elomon
Starting Member
37 Posts |
Posted - 2009-02-26 : 16:25:26
|
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
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-26 : 17:05:20
|
'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. |
|
|
elomon
Starting Member
37 Posts |
Posted - 2009-02-26 : 18:33:38
|
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? |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-26 : 20:56:56
|
Check this for all SSIS data types.http://msdn.microsoft.com/en-us/library/ms141036.aspx |
|
|
elomon
Starting Member
37 Posts |
Posted - 2009-02-27 : 09:30:20
|
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:[url]http://img7.imageshack.us/my.php?image=ssisdatatypes.gif[/url]The datatype selections don't have anything like what is listed in that page. How do I find mappings for the SSIS datatypes? |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-27 : 10:50:08
|
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 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-27 : 10:51:44
|
Dont click this hyperlink.Copy and paste the entire link in the browser including the (SQL.90).aspxIt dint get included in the hyperling for some reason |
|
|
elomon
Starting Member
37 Posts |
Posted - 2009-02-27 : 10:58:16
|
Wonderful, amazing, thank you! That is exactly what I needed. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-27 : 11:02:03
|
Welcome |
|
|
SQLDevPlus
Starting Member
1 Post |
Posted - 2011-04-11 : 02:09:49
|
To which SSIS Variable datatype should sql server Money Data Type be mapped?sqldev |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-02-05 : 10:43:56
|
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? |
|
|
|