Using variable of type Object in expression

Hi

I have some SSIS variables of type System.Object (they have to be this type because they are used to hold the results of a single row result set in an Execute SQL task which is querying an Oracle database. Although I know the Oracle table columns are Numeric, this was the only SSIS type that worked).

My problem is that I want to use these variables in expressions, but can't - I get the error "The data type of variable "User::varObjectVar" is not supported in an expression".

The only workaround I can think of is to use a script to assign the numeric values (integers, in fact) that these variables hold to other variables of type Int32.

Is that my only option, or am I missing something

thanks
- Jerzy

Answer this question

Using variable of type Object in expression

C#Thunder
The output of an Execute SQL task with a result of single row is an ADO recordset as Jamie explained and pointed you to two methods to extracting values from an ADO recordset.

If your process always returns a single row with multiple columns, why not build a stored procedure with several out parameters That way you can bypass all the scripting required with the single row result.

You may have to play around with the variable data type to get it to work, because the .Net equivalent of a Oracle Numeric is a Decimal which is not an option in SSIS. SSIS may try to do the casting under the covers though.

Larry Pope
inbissachin
Are you perhaps using Oracle 8 or 9 In earlier versions, Number columns with no decimals have caused conversion problems, which could by why you can only get Objects to work.

Which OLE DB provider are you using, MS's or Oracle's There are differences between the two providers, so you might have better luck with one or the other.

If that fails, you could explicitly cast your objects to the desired result and I would recommend using a Script Task. Just create integer forms of your variables setting the Object form as ReadOnly and the Integer form as ReadWrite and use the following code to convert.

Dts.Variables("intVar1").Value = CInt(Dts.Variables("objVar1").Value)

You can then use the integer form of the variable in your expression.

Larry Pope
Brixi69

Hi,

I have also experienced these same problems. The only solution that has worked for me is to use the oracle TO_CHAR function in your select statement and set the result set parameter to be a string variable.

Select Statement...

SELECT TO_CHAR(COUNT(DocID)) AS RECORDCOUNT FROM Documents WHERE.....

In the execute sql task set up a single row result set, and in the result set section, set up a variable named RECORDCOUNT of type string.

This works but you'll have to convert you strings back to numbers in a script task.

Rich
Me Myself and I
Sorry, should have said earlier. Its Oracle 8i.

I tried all the various providers and ended up using the .NET provider for Oracle, since I'm having to handle a lot of Oracle NUMBER fields in my Data Flow tasks (most of which are actually monetary values), and that priovider transferred them across fine.

So for the part of the system I'm referring to here, I simply re-used the exitsing .net connection manager. And yes, I ended up coming to the conclusion that using a script as you describe seemed the way to go, so thatnks for confirming that.

- Jerzy
saileshdev

You certainly cannot reference an Object variable in an expression - its only an Object after all.

You need to iterate over the contents of the recordset in your Object variable in order to do something with them. The easiest way to do this is using a ForEach Loop as described here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

If you want to go down the more torturous route of writing code then this may help you: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

I guess it depends what you want to do with it!

-Jamie
Vish
Jamie/Larry,

Thanks for your replies. I'm querying an Oracle table, so can't use a stored procedure.

Jamie's suggestions for iterating over the resultset object sound interesting (I'll certainly remember that for the future!), but I don't seem to be able to configure the Execute SQL task to place the result set into a single Object variable. If I set "Result Set" to "single row", I have to supply one variable per column, and if I set it to "full result set" and supply just one object variable as resultset name 0, it gives me an error.

The Oracle table has a DATE column followed by several NUMBER(7) columns, and so far the only approach that has worked is to supply a variable of type Object for each of the NUMBER columns in the Oracle table (the Oracle DATE field maps across to a DateTime variable no problem). SSIS does indeed set the Type of the Object variables holding the numbers to Decimal.

- Jerzy
Using variable of type Object in expression

Answer this question