Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


call sproc in oracle


call sproc in oracle

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 3855
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,
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 3855
Thanks much, will try it, may have more questions coming back, thanks
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 3855
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2043 Visits: 3855
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8235 Visits: 14368
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search