Get a table count in SSIS from an Oracle view

Question

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.)

Answer 1

I know nothing about SSIS so can't help you there, but, as a note, if you only want to check for the presence of data in a table, it's more efficient to include a ROWNUM clause in the SQL. e.g.

SELECT COUNT(*)
FROM   table
WHERE  ROWNUM < 2;

(will return 0 if the table is empty, 1 if any rows are in the table)

In this way Oracle can stop reading the results from the table/view as soon as it finds any rows, thus (potentially) finishing execution of the query much sooner.

Answer 2

SSIS has a lot of trouble talking to Oracle. I have faced similar issues before. Did you try using string data type? You will not have trouble converting string to integer in script.

Answer 3

I found a solution. It may or may not be the best solution, but it will work. Other solutions and improvements are always welcome.

First, rather than having a SQL Task, I have a Data Flow task with the Oracle source and the Data Access Mode is a SQL Command with the original shown in the question (although, I'll probably use cagcowboy's advice once this is working properly).

The output is a destination Script Transformation. I selected ROW_COUNT as my input column, didn't mess with the Inputs and Outputs, and for the Script selected my TableCount as ReadWriteVariables. My script is as below:

Public Class ScriptMain
    Inherits UserComponent
    Dim ScriptCount As Single

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Assign the variable to something outside of the sub
        ScriptCount = Row.ROWCOUNT
    End Sub

    Public Overrides Sub PostExecute()
        ' Variable is only available to set in PostExecute
        Me.Variables.TableCount = ScriptCount
    End Sub

End Class

My public variable is of type Double, and in my final script task (outside of the data flow), I simply access it with the lines

Dim nTableCount As String
nTableCount = Dts.Variables("TableCount").Value().ToString()

I suspect I should do a bit more with the data types, because it doesn't seem like it should need to be converted to a string at this point. But that's ok, I can now determine if there is data in the view, and fork accordingly.