Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

call sproc in oracle Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 1:03 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:00 PM
Points: 1,786, Visits: 3,237
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,

Post #1454695
Posted Tuesday, May 21, 2013 7:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1454996
Posted Tuesday, May 21, 2013 9:47 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:00 PM
Points: 1,786, Visits: 3,237
Thanks much, will try it, may have more questions coming back, thanks
Post #1455294
Posted Tuesday, May 28, 2013 4:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:00 PM
Points: 1,786, Visits: 3,237
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
Post #1457537
Posted Wednesday, May 29, 2013 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1457928
Posted Wednesday, May 29, 2013 4:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 2, 2014 5:00 PM
Points: 1,786, Visits: 3,237
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
Post #1457986
Posted Wednesday, May 29, 2013 6:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:14 PM
Points: 7,125, Visits: 12,719
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
Post #1458008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse