CODEWITHSUNDEEP
sql-serversql-server-2008ssis
PositiveGuy
PositiveGuy

Reputation: 47733

Working with Decimal fields in SSIS

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 E*xecute 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!!!

Upvotes: 4

Views: 7938

Answers (2)

user3780030
user3780030

Reputation: 81

I had faced similar issue, and for my solution I used decimal to Int converter.

Upvotes: 0

Tolowe
Tolowe

Reputation: 91

I would try either converting the value to a double or a varchar in your data flow task rather than an object. Note that you may have to use a cast or convert statement in your execute sql task to change the type back to decimal in your transact sql statement.

Upvotes: 3

Related Questions