Storing a Money data item (in SQL Server) in a double SSIS variable

Question

Hi

 

I have a table in SQL Server with following spec

Table1(Grossamount(money))

 

I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS

 

Select Sum(Grossamount) from Table1

 

I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.

 

it gives me the error :

[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

 

 

I tried the following sql to no avail

 

Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1

 

Your help very much appreciated

 

Answer 1

This is stupid but true situation. I workaround this using strings.

 

Feel free to rate it as a bug - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126375&wa=wsignin1.0

Answer 2

They way I managed to get around this problem was to create a script task and convert the data value that is to be assigned to the variable as double and assign to the variable of type double.

Answer 3

 Yes, I met same problem. In control flow run a execute SQL task to  get out the result  of totaldepost which defined as money in database table. When I assign this as dataset result to a variable found there is no such data type to hold it.Derived column cast function only avaible in data flow part. It seems only way to create new varibale as  string and assing the result set to this new variable , then add a new script task in control flow to convert it back to double. It works. the question is why ssis package variable setting type  there is no currency but they do have more data type selection in paramater map?.

 

Thanks

 

 

Answer 4

maybe you'll find this is an easier way..

You can cast to float in your TSQL statement or output var.  i.e. Select Convert(float, sum(myAmount)) ...   in SQL statement. 

Then SSIS is cool taking it in to a double within the Execute SQL Task. 

Answer 5

I know this is an old topic, but I had a similar problem. I was reading in a Decimal(8,0) but I was getting an error when I tried to assign that value to a variable of type double. I changed the variable to an 'object' type and it works fine now. I don't know if this will cause problems with precision but it's worth a try.