execute oracle procedure from SSIS

is it possible to execute Oracle procedure from within SSIS?

thanks

Hi Shahab,

Yes, we can do that using the Execute SQL Task.

Just specify the ConnectionType as ODBC and then select the Connection you have made to the Oracle Database.

In the SQLStatement property you can write the statement to execute the Oracle stored procedure.

Regards,

B@.ns

|||If you aren't using ODBC, use the Microsoft provided OLE DB for Oracle driver instead.

Just make sure that the Oracle client is installed first and that you have a valid tnsnames.ora file.|||

well I am using SSIS on 64 bit server. I dont see any oracle related drivers in ODBC. I am able to connect to Oracle via SSIS as I have Oracle 64 bit installed. May be it doesnt come with odbc drivers. not sure.

|||

Shahab03 wrote:

well I am using SSIS on 64 bit server. I dont see any oracle related drivers in ODBC. I am able to connect to Oracle via SSIS as I have Oracle 64 bit installed. May be it doesnt come with odbc drivers. not sure.

Don't worry about ODBC then -- I wouldn't use it anyway.|||

well I have stored procedure in Oracle which runs fine when executed from sqlplus. however when I use sql task within ssis to execute the procedure in oracle, the task fails.

the ole db connection works fine as I am using it other sql tasks.

any ideas?

|||Seeing the error message might help.|||

I have a procedure called copyPDSTables which runs fine in sqlplus. when I use sql task in SSIS and connect to oracle via ole db connection using 'execute copyPDSTables' command I get following error:

[Execute SQL Task] Error: Executing the query "execute copyPDSTables" failed with the following error: "ORA-00900: invalid SQL statement ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||What happens when you execute: 'execute copyPDSTables;' ? (Note the semi-colon at the end)|||

Try using "exec" instead of "execute".

|||

I tried using semi colon at the end. also tried exec instead of execute. but nothing works. its exact same error.

|||

well I found a workaround. instead of using pure SQL you can use plain PL/SQL code to execute the procedure.

so instead of 'execute procABC' just use

begin
procABC;
end;

It works perfectly fine.

|||

Hi the way you shown i tryied and i am getting this error.

When i am puting this to SQL Command in my OLEDB source Editor

begin
Prosun1;
end;

"No disconnected record set is availabe for the specified SQL Statement"

Let me know if you can help to resolve this issue.