asked on

SQL integration Services Data Type Issue

In an Execute SQL Task control Flow I am using the Single Row Result Set. SQL Query follows: Select Count(*) As Rec_Ct, Sum(Value) As Tot_Val From Sales_Table. The Rec_Ct field is assigned to the RecordCount(int32) variable and the Tot_Val(numeric(16,5) is assigned to the TotalValue(double) variable. Upon execution I receive the error "The type of the value being assigned to variable TotalValue differs from the current type". I have run this package without the Sum(Value) field and the package runs fine. Is it possible to use the SUM aggregate in Integration Services ? Is there another variable type I should be using ? Thanks.

DcpKing

The definition of the sum() function (here) indicates that for a decimal parameter it returns decimal(38, s).

Therefore, looking at the definitions of SSIS data types (here) I would suggest that you use either DT_NUMERIC or DT_DECIMAL. DT_NUMERIC is the larger and so may be necessary for you.

hth

Mike

StampIT

ASKER

When configuring a variable neither DT_DECIMAL nor DT_NUMERIC are options under Data Type. Boolean, Byte, Char, Dattime,Dbnull,Double,Int16, Int32, Int64, Object, SByte, Single, String, UInt32, and Uint64 are the options. I have tried Double and Single. Tried to convert into varchar and use Char for the variable. None of this worked. Thanks.

ASKER CERTIFIED SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

StampIT

ASKER

Thanks.

DcpKing

Presumably that helped! Good.

StampIT

ASKER

Yes. Using the Object data type for the variable allowed storage of the query result. Thanks.