SSIS Execute SQL Query task cannot find stored procedure

stored-procedures ssis task executequery
nleidwinger18 picture nleidwinger18 · Feb 8, 2013 · Viewed 7.7k times · Source

I am working on an existing SSIS package to add custom logging to it. I am trying to test it, and I have an Execute SQL Task that I didn't create that is getting the following error.

Executing the query

"ap_pfl_DropProfileTables"

failed with the following error:

"Could not find stored procedure 'ap_pfl_DropProfileTables'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have no idea why I'm getting this error because:

  1. I didn't create this or change it and this package is running without error in production.
  2. The stored proc just truncates two tables. It doesn't have a result set or parameters.
  3. The connections are working properly because this stored proc runs at the same time as another thread running a data flow task which runs successfully and uses the only two connections in this package.
  4. I've double and triple checked the database to make sure the stored procedure is there and spelled correctly. I even checked the case of the letters in the stored procedure.

Any ideas on how to fix this?

Answer

user1160841 picture user1160841 · Nov 4, 2013

Yes this is frustrating - but Do-able. The key is to NOT use ADO.NET connection manager but instead use the good old fashioned ADO connection manager. The 2nd key is to NOT use EXEC or EXECUTE in the SQLStatement property of the Execute SQL Task editor. Just type in the name of the stored procedure (also for good measure use the 3-part name convention database.schema.storedprocedure. )

I haven't tried this with params on the stored procedure. Also, I have not tried this with the OLE DB connection manager.

Related questions

How do you call a Stored Procedure in SSIS?

I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row. In my old DTS package, I was doing this: EXEC myStoredProcedure ?, ?, ? ...and then I mapped …

Read More
SSIS return value of Stored Procedure within an OLE DB Command

I am migrating data that has to be inserted using stored procedures which already exist. The stored procedures have parameters and a return value (from a select statement) of an id for the row inserted. Within an OLE DB Command …

Read More
Execute SSIS package from stored procedure as proxy user without xp_cmdshell

I am trying to run an SSIS package through a stored procedure, but I am getting an Access is denied error when I try to import a CSV. I put the package inside a job and ran it and it …

Read More