locked
Using Oracle procedures in SSIS RRS feed

  • Question

  • 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 to the 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 using a 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.

     

     


     

    Wednesday, April 25, 2007 12:11 PM

Answers

  • 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

       

     

    Tuesday, May 8, 2007 4:18 AM

All replies

  • Can you run the statement successfully from an Execute SQL Task?
    Tuesday, May 8, 2007 2:37 AM
  • 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

       

     

    Tuesday, May 8, 2007 4:18 AM
  • 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

    Tuesday, June 5, 2007 4:53 PM
  • 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

    Saturday, June 9, 2007 6:00 AM
  • You need this syntax when using the MS Oracle OLEDB driver (i.e. use curly brackets and a CALL statement)

     

    {call procedurename}

    • Proposed as answer by man_25 Wednesday, June 22, 2011 11:26 AM
    Tuesday, June 12, 2007 12:30 PM
  • 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) that using MS Provider for oracle works faster. Thanks for throwing light here.

    Thanks

    Subhash

    Tuesday, June 12, 2007 5:49 PM
  • 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 command

    Please help me in this regarding.

    Regards,
    Roopa
    Wednesday, September 5, 2007 4:46 AM
  • 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

    Wednesday, September 5, 2007 6:03 AM
  • 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.

     

    Wednesday, September 5, 2007 11:10 AM
  • Can you please post your alternate method in the forum, so that it can help for some body in future.

    Thanks

    Wednesday, September 5, 2007 4:25 PM
  • 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

    Thursday, September 6, 2007 4:25 AM
  •  

    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 value for the ParameterMap.  Now change the AccessMode back to "SQL Command From Variable" and you should be good to go.

    I was using the Provider=OraOLEDB.Oracle.1 and SSIS SP2.  

     

    Dan

     

     

    Tuesday, September 11, 2007 3:36 PM
  • 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.

     

     

    Thursday, March 10, 2011 10:16 PM
  • You need this syntax when using the MS Oracle OLEDB driver (i.e. use curly brackets and a CALL statement)

     

    {call procedurename}

    EXECUTE command didn't work for using using MS Oracle OLEDB driver and the Oracle Provider for OLEDB. CALL was what worked for me!
    Wednesday, June 22, 2011 11:28 AM