SSIS ORA-00900: invalid SQL statement calling Oracle Procedure

Calling an Oracle stored procedure in SSIS is a little different than calling a SQL Server version.  Today I will show you the error you get if trying the SQL Server syntax and how to fix it.

I am assuming you already have your Oracle connections set up and have rights to call the Oracle stored procedure.

The first thing I tried was using the syntax I was used to in the Execute SQL Task editor.

Below I am trying to execute the following command:

exec cisadm.CM_DIRS_MEETING_PRC_1()

 

osp1

After I click OK and then try to run the package, I get an error:

[Execute SQL Task] Error: Executing the query "exec cisadm.CM_DIRS_MEETING_PRC_1();
" 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.

 

osp2

 

After doing some research through Oracle’s documentation, you have to call the procedure as follows:

DECLARE
BEGIN
cisadm.CM_DIRS_MEETING_PRC_1();
end;

 

osp3

After these changes are made, I tried to run the package and it executed successfully.

 

osp4

 

You now know how to get Oracle Stored Procedures to run in SSIS and to solve this scary looking error message.

[Execute SQL Task] Error: Executing the query “exec cisadm.CM_DIRS_MEETING_PRC_();
” 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s