call sproc in oracle

  • We would like to do this in SSIS package:

    1. pull a list of employeeID, with their address from a SQL server table using select query (we can use Execute SQL task)

    2. Using above record set, to update the same employees in an oracle database that has a stored procedure that we can pass employeeID, and it will match employeeID to update their address.

    Can no2 be done using SSIS? can we call a stored procedure in Oracle?

    Thanks,

  • sqlfriends (5/20/2013)


    We would like to do this in SSIS package:

    1. pull a list of employeeID, with their address from a SQL server table using select query (we can use Execute SQL task)

    2. Using above record set, to update the same employees in an oracle database that has a stored procedure that we can pass employeeID, and it will match employeeID to update their address.

    Can no2 be done using SSIS? can we call a stored procedure in Oracle?

    Thanks,

    Sure. Your package would have a Data Flow containing an OLE DB Source and a Recordset Destination (really it is an ADO.NET DataSet under the covers) to capture the employeeID information from SQL Server. Then connected to the Data Flow would be a Foreach Loop Containiner setup to iterate over your DataSet (stored in a Variable of type Object). For each row in your DataSet you will use an Execute SQL Task to execute the Oracle Stored Procedure.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks much, will try it, may have more questions coming back, thanks

  • Sure. Your package would have a Data Flow containing an OLE DB Source and a Recordset Destination (really it is an ADO.NET DataSet under the covers) to capture the employeeID information from SQL Server. Then connected to the Data Flow would be a Foreach Loop Containiner setup to iterate over your DataSet (stored in a Variable of type Object). For each row in your DataSet you will use an Execute SQL Task to execute the Oracle Stored Procedure.

    Not sure I understand the first part, why I need data flow task, shall I just use a Execute SQL task with select statements?

    Could you give a step by step guide, thanks much,

    should I use oracle udpate, or use Oracle providor for OLEDB or use Microsoft OLE DB provider for Oracle?

    THanks

  • sqlfriends (5/28/2013)


    Sure. Your package would have a Data Flow containing an OLE DB Source and a Recordset Destination (really it is an ADO.NET DataSet under the covers) to capture the employeeID information from SQL Server. Then connected to the Data Flow would be a Foreach Loop Containiner setup to iterate over your DataSet (stored in a Variable of type Object). For each row in your DataSet you will use an Execute SQL Task to execute the Oracle Stored Procedure.

    Not sure I understand the first part, why I need data flow task, shall I just use a Execute SQL task with select statements?

    Sure, that's another option. If you set the Execute SQL Task to return a full resultset and map the resultset to a variable of type object you'll get the same result as what I mentioned.

    Could you give a step by step guide, thanks much,

    should I use oracle udpate, or use Oracle providor for OLEDB or use Microsoft OLE DB provider for Oracle?

    THanks

    I am not on top of the best options for working with Oracle. I have heard that the Attunity drivers are good though and I have used their offering for Teradata and have been very pleased.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • so here is what I plan to do now:

    1. create a Execute SQL task using OLEDB connection ( source SQL server) with select query and full result set.

    Set the result set to a varible of objects. and any other columns I need in the following tasks, I also assign them to variable.

    2. add a loop container,

    3, in 2, I add another execute SQL task with ADO.net connection (Oracle server) for destination.

    I am a little confusing now, should I use ADO.net connection for both source and destination (2, 3 above) or should I use OLEDB for 1 and ADO.net for 3?

    THanks

  • Your plan looks good. You'll need two different Connection Managers, one for your SQL Server and one for your Oracle instance. You could use OLE DB or ADO.NET to talk with SQL Server in 1. ADO.NET should work fine for Oracle but you have options there as well.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply