| Author |
Topic |
|
CoffeeAddict
Yak Posting Veteran
94 Posts |
 Posted - 2012-09-16 : 23:20:06
|
| I'm using SQL Server 2008 w/SP2.I've got an incoming decimal(9,2) field incoming through my OLE DB transformation to my recordset destination transformation. And I see that when I click on the Input and Output properties tab of the destination Transformation, it's showing this particular field as type DT_NUMERIC for some reason. It's like it's reading it as something other than a decimal? I don't know..I'm not an SSIS guru.So continuing on...the problem I have starts here with me trying to stuff the value into a variable for this decimal field. In a foreach loop, I have a variable to represent this decimal field so I can work with it.The first problem that I believe is pretty well known is SSIS variables do not have a decimal type. And from my own testing and what I've read out there, people are using type object for the variable to make SSIS "happy" with decimal values? It makes mine happy.But, then in my foreach loop, I have a for loop. And inside that I'm using an Execute SQL Task transformation. In it, I need to create a parameter mapping to my variable so I can work with that decimal field in my T-SQL call in here. So now I see a type decimal for the parameter and use it and set that to point to my variable.When I run SSIS and it hits my SQL call, I get this in my output window.: The type is not supported.DBTYPE_DECIMAL So I am hitting a wall here. All I wanna do is work with a decimal!!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 02:04:04
|
| Numeric and Decimal are similar types so they're mutually compatibleObject variable is not replacement for decimal it stores a set of values rather than a single valueFor you to get individual values from loop you need a variable of type Double and that's the variable you should be mapping in parameters tab of execute sql task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
CoffeeAddict
Yak Posting Veteran
94 Posts |
Posted - 2012-09-17 : 02:16:17
|
I've tried that already...it does not work and errors out.quote:
Originally posted by visakh16
Numeric and Decimal are similar types so they're mutually compatibleObject variable is not replacement for decimal it stores a set of values rather than a single valueFor you to get individual values from loop you need a variable of type Double and that's the variable you should be mapping in parameters tab of execute sql task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 11:17:38
|
| it will if you do it properly. where are trying to use DBTYPE_DECIMAL cast logic? can you explain the flow of your package from loop to place where you assign variable value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
CoffeeAddict
Yak Posting Veteran
94 Posts |
Posted - 2012-09-17 : 12:01:00
|
| the problem is I can't even get that far. I'm not able to get my DT_NUMERIC into a freakin SSIS variable because when I try double as the ssis variable type I get "The type of the value being assigned to variable [the field I'm talking about in this discussion] differs from the current variable type" when I run my package. |
|
|
|
CoffeeAddict
Yak Posting Veteran
94 Posts |
Posted - 2012-09-17 : 12:20:19
|
| was able to convert the value to DT_R8 then use type double for my ssis varable and double for my sql task param and it works but I would rather not use DT_R8 as this is not a float, it's a decimal incoming so that could produce some off results if I force it into a DT_R8 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 13:10:29
|
| i feel issue is way you get values. whats source data type of this?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
CoffeeAddict
Yak Posting Veteran
94 Posts |
Posted - 2012-09-17 : 13:53:00
|
| The issue really is figuring out what makes SSIS happy in the 100 combinations switching between data types from the SSIS data types, to SSIS variable types, to transformation parameter types. It's a nightmare. But, there are several "combinations" that do work...but they are mostly weird. For instance you can go from DT_NUMERIC from a data source (OLE DB) to SSIS variable of type Object to SQL Task parameter of type Numeric. That's just one that worked, and I found more combinations after a while. For anyone new to this, it sucks. Shouldn't be this hard ssis! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-17 : 14:29:55
|
| object is not a specific data type but its rather an array which stores a group of values. so dont confuse it with any other types. Also SSIS being an ETL is very strict on datatypes so unless you do proper casting it will error out inidcating that there's a data mismatch.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Working with decimal fields in SSIS Problem