In my SSIS package, I want to see if a particular Oracle view has data in it. I have a Execute SQL Task with a select count statement:
Select CAST( count(*) as Integer) As Row_Count from OWNER.VIEW_MV
My RowCount is in the Result Set, pointing to my Variable User:TableCount. If I set TableCount to anything except Object, I get an error such as:
Error: 0xC002F309 at Check for data in view, Execute SQL Task: An error occurred while assigning a value to variable "TableCount": "The type of the value being assigned to variable "User::TableCount" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
However, if I do make the data type to Object, then in my next step, a Script Task, when I read that object, I don't see how to convert it to an integer so that I can use and view it. Then I get the error:
Conversion from type 'Object' to type 'Integer' is not valid.
with the code
Dim nTableCount As Int32
nTableCount = CInt(Dts.Variables("TableCount").Value)
Perhaps I am going about this wrong. What is the best way to determine if an Oracle table is empty, and then act on that knowledge? Essentially, we want an error message about the empty table, rather than continuing on in our process.
Update: I've tried Select CAST( count(*) as char) As Row_Count from OWNER.VIEW_MV where ROWNUM < 2, and sending it to a SSIS variable of type char. I've cast it to Numeric and Integer, with SSIS variables of Int32, Int64. I've tried varchar2(20) with SSIS type String. Everything gives an error except for SSIS datatype Object.
Right now, I'm saving as datatype Object, but when I try to get the value, setting my nTableCount as String, I can use nTableCount = Dts.Variables("TableCount").Value().ToString(), but it returns 0. How do I extract that string out of the Object variable? (Assuming of course, that it actually put it in there.)