Using Oracle procedures in SSIS
Hi Everyone Please pardon my inexperience, I am new to SSIS. I am having some difficulty with using Oracle procedures in SSIS. I have installed and configured the Oracle Client Software for 10g.I have managed to create a connection tothe Oracle database that I will be using. I am currently using the Oracle Provider For OLE DB. I want to add an OLE DB Source component to the Data Flow which I then want to configure to use an Oracle procedure to bring back the data that I need. When I want to execute the Oracle Proc in the same way that I would normally execute a SQL Proc it returns an error saying: TITLE: Microsoft Visual Studio------------------------------ Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.". Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap) I have tried usinga ADO.NET connection and then using a Data Reader Source component but I get an error with my SQL Statement saying "Invalid SQL Command" Could anyone please provide me with some information on what I am doing wrong? Or possibly point me to information that will help me? I have been searching the net non-stop without success.
April 25th, 2007 3:11pm

Can you run the statement successfully from an Execute SQL Task?
Free Windows Admin Tool Kit Click here and download it now
May 8th, 2007 5:37am

Hi Swinx, Try the following 1) First execute the stored procedure in Sqlplus prompt, if it works fine, then Copy this code 2) Secondly make sure you can successfully connect to tables and other objects in oracle (using simple sql commands) 3) Thirdly, The driver I'd like to suggest for oracle is Microsoft driver for Oracle, While creating Connection manager. 4) Fourthly, If you do not have any parameters, you may directly paste the PL/SQL into Excute SQL task. 5) Finally if you are using parameters, please be careful about input column numbering which depend on the driver (.NET Provide, OLEDB, ODBC) you'll be using. Thanks Subhash Subramanyam
May 8th, 2007 7:18am

Hi Swinx, Did Subhash's reply was helpful? How did you manage to solve the problem and which connection did you use? I have the same problem and trying to solve it myself. Please reply to: iram.levinger@comverse.com Thanks
Free Windows Admin Tool Kit Click here and download it now
June 5th, 2007 7:53pm

Hi iram, Would you please be specific, what error you got while running the stored procedure. Are you passing any parameters? If not Dataflow task, But using Execute SQL Task, are you retrieving any results in one of the forms (Single row, Full ResultSet, or Value). Thanks Subhash Subramanyam
June 9th, 2007 9:00am

You need this syntax when using the MS Oracle OLEDB driver (i.e. use curly brackets and a CALL statement) {call procedurename}
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2007 3:30pm

In fact I used Oralce provider when I answered swinx. I read through an article by Scott (who has extensively worked on SSIS using Oracle) thatusing MS Provider for oracleworks faster.Thanks forthrowing light here. Thanks Subhash
June 12th, 2007 8:49pm

Hi,I am using Oracle Provider for oledb. i have to execute an update statement. But when i select this Provider i am getting the following error:Error at Data flow task[oledb command[8717]]: An oledb error has occured. Error Code:0x80040E51. An Ole db record is available. Sourece:"OraOledb" Descrition:"Provider cannot derive parameter information and SetParameterInfo has not been called"Unable to retreive destination column descriptions from the parameters of the sql commandPlease help me in this regarding.Regards,Roopa
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2007 7:46am

As Subhash suggested earlier in the thread, try using Microsoft OLE DB provider for Oracle. I read some where in the internet that Oracle OLE DB Provider behaves wierd in certain situations, unfortunately I dont recall the url.Thanks
September 5th, 2007 9:03am

Thanks for your information. I had used that provider also however the error is the same. Now I had find alternative method for that. Thanks a lot.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2007 2:10pm

Can you please post your alternate method in the forum, so that it can help for some body in future.Thanks
September 5th, 2007 7:25pm

Instead of Oledb command i used Script Component as destination. So in the script i wrote Command to update the rows which is coming out from the conditional split transformation. Regards, Roopa
Free Windows Admin Tool Kit Click here and download it now
September 6th, 2007 7:25am

I found out a solution to what I think is an SSIS bug for the error message: "Provider cannot derive parameter information and SetParameterInfo has not been called" If you change the AccessMode from "SQL Command From Variable" to "SQL Command" delete the valuefor the ParameterMap. Now change the AccessModeback to"SQL Command From Variable" and you should be good to go. I was using the Provider=OraOLEDB.Oracle.1 and SSIS SP2. Dan
September 11th, 2007 6:36pm

this i s an old thread so i think some of this info is no longer relevant, i dont think MS has an oracle provider or is no longer supporting the MS oracle driver. we switched over to the ORACLE OLEDB driver a while back, the correct syntax i found for claling a stored procedure is the one mentioned above by Mark Challen {call <schema>.<Proc name>} i'm not sure if you have parmters but i suspect ? would be used. the # for the parameters may be different it may start with 0 or 1. this was on SQL 2005 running SSIS in VS Studio 2005.
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2011 5:51am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics