SSIS 2012 stored proc from execute sql task

1
votes

I am trying to execute a very simple stored procedure from a SSIS 2012 Execute SQL Task. I have reduced the procedure to a stub to troubleshoot this - no parameters or result set. The procedure simply updates a single field in a table. I am using an OLE DB connection and the SQL statement is "EXECUTE dbo.MyProcedure". Could not be simpler. When I run the package from visual studio no errors are generated but the proc has not run.

Some clues: If I click "Build Query" from the task editor I get "The EXECUTE SQL construct or statement is not supported." If I click "OK" and run the query from the query builder screen it succeeds and the proc has run! I can see the update in the table.

When running the package from visual studio I can see the "EXECUTE dbo.MyProcedure" call in SQL Profiler but the proc does not run!

Has anyone encountered this?

Thanks!

4
sql-serverstored-proceduresssis
Did you set results to none? - user275683
Don't click build query. That isn't set up do deal with "real" sql - billinkc
possible duplicate of SSIS Stored Procedure Call - user275683
Is your SQLSourceType Direct Input or Stored Procedure? When the latter is true you'll have to leave out the EXECUTE command - NickyvV
Yes results is set to none. Linked question was solved by " ... I found the issue was importing a varchar (Max)". I have no parameters, returns or varchar(Max) so I don't think this is a duplicate. - jjm

4 Answers

0
votes

Set QueryIsStoredProcedure property to True

http://technet.microsoft.com/en-us/library/ms188957.aspx

0
votes

Stored procedure ran fine after a re-start of visual studio. Thanks NickyvV!

0
votes

Try using EXEC rather than EXECUTE.

-1
votes

NO NEED TO USE EXECUTE or EXECU...just write below syntax in execute sql task.

BEGIN
<procedure name>;
END;