Clever Geek Handbook

SSIS Execute SQL Query cannot find stored procedure

I am working on an existing SSIS package to add custom log to it. I am trying to test it and I have an Execute SQL Task that I have not created, which is getting the following error.

Executing a request

"ap_pfl_DropProfileTables"

failed following error:

The ap_pfl_DropProfileTables stored procedure could not be found. " Possible reasons for the failure: problems with the request, the "ResultSet" property was not set correctly, the parameters were not set correctly, or the connection was not established correctly.

I have no idea why I am getting this error because:

  • I have not created this or modified it and this package works without error in production.
  • The stored procedure simply truncates two tables. It has no result set or parameters.
  • The connections work correctly because this stored proc is running concurrently with another thread doing a data flow task that succeeds and uses only two connections in this package.
  • I checked the double and ternary database to make sure the stored procedure is written correctly there. I even checked the case of letters in the stored procedure.

Any ideas on how to fix this?

+3


source to share


4 answers


Yes, it's frustrating, but Do-able. The key is NOT to use the ADO.NET Connection Manager, but instead use the good old ADO Connection Manager. The second key is NOT to use EXEC or EXECUTE in the SQLStatement property of the Execute SQL Task editor. Just enter the name of the stored procedure (also use the database.schema.storedprocedure three-part assignment convention for a good estimate.)



I have not tried this with stored procedure parameters. Also, I haven't tried it with OLE DB connection manager.

+2


source


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

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



I think that when IsQueryStoredProcedure is set to true, the object automatically adds EXEC to identify that the command is a call to a stored procedure.

+1


source


I know this is an old thread, but I just ran into this problem when using SSIS in SQL 2008 R2.

For me with an ADO.NET connection I actually needed to install IsQueryStoredProcedure

in False

and then the error went away. It doesn't matter if I used it EXEC

or not.

+1


source


After the same problem, I did some research on this:

Specifically my situation:

  • I need to use ADO.Net because I am running against SQL Azure
  • I want to write the return value of a stored procedure

I tried this first:

In SQLStatement, I put proc name (no EXEC)

myschema.MyProc;

In IsQueryStoredProcedure I put False

In ResultSet I have set None

In the Parameter Mapping tab, I have placed

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

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

My guess is that if you set IsQueryStoredProcedure to true, it should do it all right. But instead, it returns an error.

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

As a workaround, I did this:

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

I left IsQueryStoredProcedure as False

I have set the ResultSet to single.

I removed the parameter mappings and displayed the result set instead:

Result Name  Variable Name
     0          User::MyVariable
0


source






More articles:


All Articles