1

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!

jjm
  • 41
  • 1
  • 5
  • Did you set results to none? –  Mar 12 '14 at 19:52
  • 1
    Don't click build query. That isn't set up do deal with "real" sql – billinkc Mar 12 '14 at 20:00
  • possible duplicate of [SSIS Stored Procedure Call](http://stackoverflow.com/questions/17787430/ssis-stored-procedure-call) –  Mar 12 '14 at 20:02
  • Is your SQLSourceType `Direct Input` or `Stored Procedure`? When the latter is true you'll have to leave out the EXECUTE command – NickyvV Mar 12 '14 at 20:13
  • 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 Mar 12 '14 at 20:15
  • I tired leaving out EXEC with the same negative results. – jjm Mar 12 '14 at 20:17
  • I only clicked build query out of desperation for some kind of clue. I would never use this otherwise. – jjm Mar 12 '14 at 20:18
  • What happens when you execute the command in SSMS? – NickyvV Mar 12 '14 at 20:31
  • Works from SSMS. I wish I could offer more clues but I am stumped! – jjm Mar 12 '14 at 20:32
  • Sounds funny, but have you tried closing and opening VS? – NickyvV Mar 12 '14 at 20:42
  • NickyvV that's a very good idea! I have to check out till tomorrow but I will try that first thing in the morning and let you know. Thank you very much. – jjm Mar 12 '14 at 20:50
  • How do you know the proc hasn't run if it doesn't do anything? Do you mean the task doesn't turn green? Why don't you monitor it using Profiler and check what is being submitted. Perhaps there are some expressions set on the task or some configs that alter the task behaviour during runtime. – Nick.Mc Mar 13 '14 at 01:36
  • NickyvV you nailed it! Proc. runs fine after a restart of visual studio (actually a restart of the computer). How frustrating! – jjm Mar 13 '14 at 13:21
  • Thanks everyone for your suggestions. ElectricLlama - the proc was updating a field as stated in the question. That's how I knew if it was working or not. – jjm Mar 13 '14 at 13:22

4 Answers4

0

Set QueryIsStoredProcedure property to True

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

Oscar
  • 13,594
  • 8
  • 47
  • 75
  • Thanks Oscar but this option is only available for ADO.net connections. My connection is OLE DB – jjm Mar 12 '14 at 19:58
0

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

jjm
  • 41
  • 1
  • 5
0

Try using EXEC rather than EXECUTE.

J.S. Orris
  • 4,653
  • 12
  • 49
  • 89
-1

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

BEGIN
<procedure name>;
END;
Michael Eakins
  • 4,149
  • 3
  • 35
  • 54
satyajit
  • 1