What variable type use to represent a Money in SSIS

Question

I need to load a Money value in a variable in SSIS using a "Execute SQL Task" component. I map the return column of a SELECT to a variable. I've declared the variable "UnknownMoney" as a Double or a Single, but i always receive the error:

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

The Money column type in SSIS is DT_CY (Currency) but this type is not available for variables.

What's the right type to be used for a Money?

Answer 1

SSIS doesn't have an easy way to get values with SQL Server MONEY data type into SSIS variables. The best choice for an SSIS variable data type is Double. However, the MONEY value will not implicitly convert to a Double. To make it work, cast the MONEY value to FLOAT in the SQL query.