Post reply

Execute Package Task vs Individual steps in sqlAgent

  • May 3, 2017 at 5:35 pm

    #337699

    Hi,

    I have a requirement to run 3 SSIS packages one after the other, such that 2nd one should succeed if 1st one does, and 3rd one should succeed of 2nd one does.

    I have two choices.

    1) Create an Execute Package Task and knit all the 3 packages inside it with a dependency and invoke the Master package through sqlAgent job

    2) Leave the individual SSIS packages as they are and create 3 steps in the sqlAgent job and set the "on Success" criterion

    Which one would be a better option.  Please share your thoughts.  If option 2 is better, why in the first place we have EXECUTE PACKAGE task at all?

    thank you

  • May 3, 2017 at 10:37 pm

    #1941034

    If you are simply executing packages one after the other with the only constraint being that one must finish successfully before the other then there is no real difference between using SQL Agent with 3 job steps or a master SSIS package with 3 execute package tasks. 
    Using a master package you have one more item to manage is all I would say is the difference.  Chances are that both implementations will need SQL Agent Jobs to execute them

  • May 4, 2017 at 8:44 am

    #1941118

    If you keep the 3 SSIS packages separate and have a SQL Agent job with multiple steps, it will be much easier to rerun the later steps if a step fails, since you can specify what step a job starts with if you run it manually.  Doing that from within a master SSIS package would be more difficult, and require executing individual tasks from within Visual Studio (BIDS or whatever it's called this year)

  • May 5, 2017 at 6:35 pm

    #1941402

    Chris Harshman - Thursday, May 4, 2017 8:44 AM

    If you keep the 3 SSIS packages separate and have a SQL Agent job with multiple steps, it will be much easier to rerun the later steps if a step fails, since you can specify what step a job starts with if you run it manually.  Doing that from within a master SSIS package would be more difficult, and require executing individual tasks from within Visual Studio (BIDS or whatever it's called this year)

    Agreed - much easier to rerun from step 2 or 3 with just the 3 packages and a SQL Agent Job

Viewing 4 posts - 1 through 3 (of 3 total)

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