execute oracle procedure from SSIS
is it possible to execute Oracle procedure from within SSIS? thanks
September 18th, 2007 10:46pm
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
September 18th, 2007 10:49pm
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.
September 18th, 2007 10:53pm
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.
September 18th, 2007 10:53pm
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.
September 19th, 2007 12:36am
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?
September 19th, 2007 3:38am
Seeing the error message might help.
September 19th, 2007 8:59am
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.
September 19th, 2007 5:06pm
What happens when you execute: 'execute copyPDSTables;' ? (Note the semi-colon at the end)
September 19th, 2007 6:41pm
Try using "exec" instead of "execute".
September 19th, 2007 6:47pm
I tried using semi colon at theend. also tried exec instead of execute. but nothing works. its exact same error.
September 19th, 2007 6:54pm
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 beginprocABC;end; It works perfectly fine.
September 20th, 2007 3:39pm
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 beginProsun1;end; "No disconnected record set is availabe for the specified SQL Statement" Let me know if you can help toresolve this issue.
September 21st, 2007 8:40am
Hi, I used begintest1;end; and it is giving - SSIS package "Package.dtsx" finished: Success, but it is not doing anything. "test1" procedure has couple of insert statements and a commit statements but the package is not inserting anything. Also when I use "exec test1;"it is failing with the error mentinoed here in the forum. Any idea what is wrong here?
June 22nd, 2008 8:38pm
Oh Thank you, SQLStar! I've been trying to figure this out for about a week now. Works like a charm in my "Execute SQL Task" box for my ODBC connection.
December 29th, 2010 3:08pm