Monday, November 05, 2007 6:05 AM
I have a table in SQL Server with following spec
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
Monday, November 05, 2007 9:44 AMModerator
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
Monday, November 05, 2007 11:43 PM
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.
Friday, November 30, 2007 4:55 PM
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?.
Saturday, February 09, 2008 3:44 AM
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.
Wednesday, August 22, 2012 1:30 PMI 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.