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()

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.

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;

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

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.