Capturing record count for a table in Oracle and saving it in a table in SQL Server

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

any idea?

thanks

Why are you trying to use a Script Task? This can be achieved very easily using an Execute SQL Task.

-Jamie

|||

I'd concur with Jamie, use an Execute SQL task.

If you have to use the script task, then you need to reference the System.Data.OleDb namespace for OLEDB connections, or the System.Data.OracleClient for the Oracle client.

|||well how do I use sql task to accomplish this?|||select count(*) from table

Store the contents of that SQL statement into a single result set. Variable mapped to 0 in the Result tab.|||the result tab is frozen. how do I enable it? do I need to define an expression?|||

Set ResultSet='Single Row'

-Jamie

|||is that an expression?|||

No. ResultSet is a property of the Execute SQL Task.

-Jamie

|||gotcha.. i did set it single row. Now how do I get the value from that resultset and use it to update a column.|||On the Result Set tab, add a result, make the name 0 and specify the variable to put the count in. Then use another Execute SQL task, with an Insert statement, to write the variable to your table.|||

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

|||No, 0 refers to the first column of the resultset (it's a zero-based index).|||

Shahab03 wrote:

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

INSERT INTO MyTable (RecordCountCol)

VALUES (?)

On the Parameter Mapping tab, add a parameter, set the variable to the same one you populated earlier, make the name 0, and set the data type appropriately.

See this for a really good overview of using the Execute SQL task. http://www.sqlis.com/58.aspx

|||

well I have a sqltask now with following properties:

query: select count(*) as EmpCompRC from empcomp

I have also defined a variable EmpCompRC. Result name in result set tab is set to 0 and variable is EmpCompRC.

Now as I understand I have setup another sql task after the defined above. Correct?

and what would that task look like? I dont see how would I pass the variable value from previous task to this one. I tried following statement but it failed:

update detail

set sourcerecordcount = @.EmpCompRC

Go

does anyone know how to setup this sqltask? query above still leaves the variable name out.