Storing a Money data item (in SQL Server) in a double SSIS variable
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
November 5th, 2007 1:05am
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
November 5th, 2007 4:44am
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 flowto 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
November 30th, 2007 11:55am
maybe you'll findthis is aneasier 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.
February 8th, 2008 10:44pm
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.
August 25th, 2012 10:52am