Search code examples
typesssiscurrencyssis-data-types

What variable type use to represent a Money in SSIS

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?

EDIT: the variable type drop down listbox in VS2012:

variable type listbox 2 variable type listbox 1

Solution

  • This is what I would do:

    1. Have the SQL return a type decimal with a Select CAST(MoneyColumn AS decimal(34,8)) FROM Table
    2. Set it to your UnknownMoney of type Decimal
    3. Use your UnknownMoney variable.
    4. If you need to use the variable as a money again, CAST it back into money: CAST(@UnknownMoney AS money)