Post reply

How do you execute a stored proc from an Execute SQL Task?

  • February 18, 2010 at 3:02 pm

    #219324

    Is there some sort of trick to run a stored procedure from an Execute SQL Task?

    I have a stored procedure, mydatabase.dbo.spFoo. It takes a single input parameter, and is simply updating a table in the database. There should be no output, no results or anything.

    In SSMS, if I run "exec mydatabase.dbo.spFoo @parmID", everything works great and the stored proc runs in a few seconds. I want to run this in my SSIS package, passing in a variable.

    In my SQL Task Editor, I have the parameter mapped. I know this works because I previously had the SQL inline, contained in the task. Everything worked fine, but we want to move the query outside to a stored proc so it's easier to maintain. So what is the proper syntax to execute a stored procedure in the SQL task?

    I have a connectiontype of ADO.NET. The connection is to the correct database. SQLSourceType is DirectInput.

    My SQLStatement value is mydatabase.dbo.spFoo.

    IsQueryStoredProcedure is set to True.

    The task runs, or at least doesn't give an error when running, but is not finishing; it's just hanging there.

    If I change IsQueryStoredProcedure to false, and set the statement to "exec mydateabase.dbo.spFoo @parmID" I get the same issue - it runs without an error but pretty much hangs.

    I've seen mention online of some sort of "?" variable passing syntax, and I'm not sure why when I set IsQueryStoredProcedure to True how it's apparently automatically including the variable on it's own.

    MS's documentation on *how* to actually run this is sparse, or at least I'm missing it or something. Any help? Thanks!

  • February 18, 2010 at 4:10 pm

    #1121462

    in the Execute SQL Task command area, type the name of the stored procedure and the parameter variable value

    ie....

    exec INS_NewColor 'purple'

    That is it.

    In the sproc you should have code that verifies that the record does not already exist, and you should have code that tells you that one new row was inserted with success.

    Andrew SQLDBA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply