5

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?

StuperUser
  • 10,555
  • 13
  • 78
  • 137
nleidwinger18
  • 169
  • 1
  • 2
  • 12
  • 5
    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? – Goose Feb 08 '13 at 17:44
  • 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 – nleidwinger18 Feb 08 '13 at 17:51
  • Have the parameters changed at any point and/or are they mapped correctly? – Goose Feb 08 '13 at 18:07
  • 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. – nleidwinger18 Feb 08 '13 at 18:17
  • There are no parameters. The stored procedure simply truncates two tables. – nleidwinger18 Feb 08 '13 at 18:23
  • 1
    What happens if you change the query to `exec dbo.ap_pfl_DropProfileTables`? – Pondlife Feb 08 '13 at 19:21
  • 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! – nleidwinger18 Feb 08 '13 at 19:56
  • Perhaps someone can answer this question to explain the results you found. Thanks for letting us know, however :) – Tim Post Feb 11 '13 at 02:48
  • 1
    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. – Nick.Mc Feb 11 '13 at 06:32

4 Answers4

4

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.

joehanna
  • 1,471
  • 1
  • 11
  • 22
3

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.

user1160841
  • 31
  • 2
1

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.

xwraith
  • 23
  • 1
  • 3
0

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
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91