Get table counter in SSIS from Oracle view
In my SSIS package, I want to see if there is a specific kind of Oracle in it. I have an Execute SQL Task with a select count clause:
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 other than Object, I get an error like:
Error: 0xC002F309 while validating data in view, Execute SQL Task: error when assigning value to variable "TableCount": "The type of the value assigned to the variable" User :: TableCount "is different from the type of the current variable. Variables may not change type at runtime. Variables types are strict, except for variables of type Object.
However, if I make the datatype an Object, then in my next step, Script Task, when I read this 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 invalid.
with code
Dim nTableCount As Int32
nTableCount = CInt(Dts.Variables("TableCount").Value)
Perhaps I am going to get it wrong. What is the best way to determine if an Oracle table is empty and then act according to this knowledge? Basically, we want to receive an error on an empty table, not continue in our process.
Update: I tried to select CAST (count (*) as char) As Row_Count from OWNER.VIEW_MV where ROWNUM <2 and send it to SSIS variable of char type. I passed it in numeric and integer, with SSIS variables for Int32, Int64. I have tried varchar2 (20) with SSIS type string. Everything gives error except for the object of SSIS datatype.
I am currently storing a Dataatype Object, but when I try to get the value by setting my nTableCount as a String, I can use nTableCount = Dts.Variables ("TableCount"). Value (). ToString (), but it returns 0. How do I extract this string from the Object variable? (Assuming, of course, it's actually there.)
source to share
I found a solution. This may or may not be the best solution, but it will work. Other solutions and improvements are always welcome.
Firstly, instead of having a SQL task, I have a data flow task with an Oracle source, and the data access mode is a SQL command with the original given in the question (although I will probably use cagcowboy's advice after this works fine).
The output is a Script transformation. I selected ROW_COUNT as input column, didn't mess with inputs and outputs, and for Script selected my TableCount as ReadWriteVariables. My Script looks like this:
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 last Script task (outside the dataflow) I just access it with strings
Dim nTableCount As String
nTableCount = Dts.Variables("TableCount").Value().ToString()
I suspect I have to do a little more with data types because it doesn't seem like it needs to be converted to a string at this point. But that's okay, now I can determine if there is data in the view and, accordingly, a fork.
source to share
I don't know anything about SSIS, so I can't help you, but if you want to check if there is data in a table, it's more efficient to include the ROWNUM clause in SQL. eg.
SELECT COUNT(*)
FROM table
WHERE ROWNUM < 2;
(will return 0 if the table is empty, 1 if any rows are in the table)
Thus, Oracle can stop reading results from the table / view as soon as it finds any rows, thus (potentially) completing the query much earlier.
source to share
SSIS has a lot of problems with Oracle. I faced similar problems earlier . Have you tried using the string data type? You will have no problem converting a string to an integer in your script.
source to share