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 2008 Forums
 SSIS and Import/Export (2008)
 Working with decimal fields in SSIS Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/16/2012 :  23:20:06  Show Profile  Reply with Quote
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!!!

Edited by - CoffeeAddict on 09/16/2012 23:34:35

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/17/2012 :  02:04:04  Show Profile  Reply with Quote
Numeric and Decimal are similar types so they're mutually compatible

Object variable is not replacement for decimal it stores a set of values rather than a single value

For 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/17/2012 :  02:16:17  Show Profile  Reply with Quote
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 compatible

Object variable is not replacement for decimal it stores a set of values rather than a single value

For 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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/17/2012 :  11:17:38  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/17/2012 :  12:01:00  Show Profile  Reply with Quote
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.
Go to Top of Page

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/17/2012 :  12:20:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/17/2012 :  13:10:29  Show Profile  Reply with Quote
i feel issue is way you get values. whats source data type of this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 09/17/2012 :  13:53:00  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/17/2012 :  14:29:55  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000