SSIS Execute SQL Query task cannot find stored procedure

Question

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?

Wisconsin

Are you sure your connection string is pointing to the correct location? Does the sproc exist in the given schema? Does the account executing the sproc have the correct permissions to access it? – Morphine

The connection string is good because the Data Flow Task that runs at the same time uses the same connection and it completes successfully without error. I am using the system administrator account so yes it has access. The stored procedure is created under dbo – Wisconsin

Have the parameters changed at any point and/or are they mapped correctly? – Morphine

I have checked the database that Execute SQL Task connects to and the stored procedure exists there. I can run the stored procedure from SQL Server Management Studio and it works. – Wisconsin

There are no parameters. The stored procedure simply truncates two tables. – Wisconsin

What happens if you change the query to exec dbo.ap_pfl_DropProfileTables? – Officialdom

I got it to work. I simply created a new connection and set it up exactly the same way as the connection I was originally using, and it worked. I have no idea why it worked, but it did. Thanks! – Wisconsin

Perhaps someone can answer this question to explain the results you found. Thanks for letting us know, however :) – Selfoperating

Perhaps there was an expression in your misbehaving connection that gets evaulated dynamically, which may not be obvious ni the SSIS design surface. One way to check for sure is use SQL Profiler to monitor the database that you expect the query to execute against and verify that it is indeed running against the database that you expect. Something else you could do is extract the connections from the SSIS XML and compare them visually and see if anything is different. – Nally

Answer 1

I know this is an old thread but I have just run into this issue when using SSIS on SQL 2008 R2.

For me with an ADO.NET connection, I actually had to set IsQueryStoredProcedure to False and then the error went away. It didn't matter whether I used EXEC or not.

Legalese

Answer 2

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.

Obtest

Answer 3

I ran into this myself, and here is what I did (with the ADO.NET connection)

In the SQLStatement field I put the name of my stored procedure (dbo.myStoredProc). I then set the IsQueryStoredProcedure property to "True"

I'm thinking that when IsQueryStoredProcedure is set to true the object automatically prepends EXEC to identify that the command is a stored procedure call.

Gertrudis

Answer 4

After having the same issue I did some investigation on this:

Specifically my situation is:

1. I need to use ADO.Net because I am running against SQL Azure
2. I want to capture the stored procedure return value

First I tried this:

In SQLStatement I put the proc name (without EXEC)

myschema.MyProc;

In IsQueryStoredProcedure I put False

In ResultSet I put None

In the Parameter Mapping tab I put

Variable Name      Direction    Data Type    Parameter Name   Parameter Size
User::MyVariable  ReturnValue     Int32            0                 -1

This runs without error, but does not capture the return value.

I assume if you set IsQueryStoredProcedure to true, it should wire all this up properly. But it returns an error instead.

This https://technet.microsoft.com/en-us/library/cc280502(v=sql.110).aspx, says for capturing the return value when using ADO.Net"Set IsQueryStoreProcedure is set to True". But it returns the error that is the OP

As a workaround I did this:

DECLARE @R INT
EXEC @R = MySchema.MyProc;
SELECT @R

I left IsQueryStoredProcedure as False

I set ResultSet to singlerow

I removed the parameter mappings and instead mapped a resultset:

Result Name  Variable Name
     0          User::MyVariable

Nally