Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Independent SSIS Package Execution

Proposed Independent SSIS Package Execution

  • Saturday, December 29, 2012 2:51 PM
     
     
    Sign In to Vote
    0

    Hi Everyone,

    I am looking for a way to execute multiple instances of child package independently of the parent, once I pass the variables.

      

    1. The parent package keeps running in an infinite loop and keep scanning a table every few seconds (infinite FOR loop ).
     2. Based on conditions, a record might be picked up and passed to a child package through an Execute Package task.

    I would like to run multiple instances of the child package by in parallel by adding an execute package task (which is the final task in the package).
     However, I would like to ensure that in case one instance of child package is executing, the parent package should not be waiting at the last step for each child instance to complete. It should continue scanning (that is the control flow should start from the begining) and invoke more instances of child package. The control seems to return to the parent package from the execute package task after the child package execution complete.
     I am wondering if setting the Execute package task to run "Out of process" will accomplish this task.

    If yes, what are the ways in which the number of parellel instance executions can be controlled?

    I am using a "Execute SQL task" to check the value of a variable, based on which the "Execute Package" task is executed. I am wondering on how I can have multiple "Execute Package" tasks executing at the same time.
    Current the parent package runs in an infinite FOR loop.
    Could you please point to some details on how to use Script task?

    Currently I am planning to have multiple Execute package tasks immediately after the ExecuteSQL task and believe that they should start executing at the same time. Is this correct?

     

     By the way, I get "SSIS pipeline error: to execute package out of process you must install <task name>". I have SSDT installed on my VM. Should be deploying both parent and child, on a server with SSIS installed, to get it workiing?

    Thanks for your help!!

     

All Replies

  • Wednesday, January 02, 2013 3:04 PM
    Moderator
     
     Proposed
    Sign In to Vote
    0

    My opening remark is - running packages in an infinite loop is not a good practice. Thing is you can hardly control/detect a task completion before running next. Deadlocks are almost imminent in this scenario, especially if you run SQL updates.

    Use something else, e.g. a on insert trigger to pick the newly inserted rows.

    The package should run periodically and process data.

    Regarding the error: let's make it a new thread and please provide more info re how you run the package and show us a screenshot of the error. SSDT may not be at fault, by the way.

    Arthur My Blog

     
  • Wednesday, January 02, 2013 10:49 PM
     
     
    Sign In to Vote
    0

    I will agree with Arthur Mac.  No one is going to tell you how to evolve an inherintly bad process to begin with. 

    Running an infinite loop in SSIS (or anything else) is not a good program design.  You should be using some type of task schedule to run the package intermittenly to completion.  If you are looking to "realtime" monitor a table, there are much better ways to do so.  The truth of the matter is that nothing is 100% "realtime"  it's just how little latency is acceptable.  But you shouldn't be running infinite loops to make something "realtime"

    There is nothing wrong with running an SSIS package every 30 Seconds or even less, if the package only takes 5 seconds to run. SSIS caches packages in memory so it is quite efficient at doing just that.

    If you really want high degrees of parallel execution the Biz Talk environment is more robust than SSIS, as SSIS is considered a Point to Point application.  However I think you will need to elaborate a bit more on what is you are trying to do and why you are trying to do it this way before soemone can suggest a solution.

    You basically want "near realtime" monitoring of a table, and then based on the results and values found in those records, want to execute some type of further action correct?

     
  • Friday, January 04, 2013 7:54 AM
     
     
    Sign In to Vote
    0

    Yes. that is correct. Thanks for your help. I actually did realize that running infinite loop might not be a good idea.

    I should be able to manage with a latency of a few seconds without problems.

    The main issue at the moment is as below:

    1. Parent package is fired

    2. Checks the table and if records are found control is then transferred to a sequence container.

    3 Multiple instances of the child package are fired in parellel. (example 3).

    The 2 things I am looking at are:
    a. How to get my parent package to complete its execution and run these parallel instances run independently, so do not have to wait for the child package to finish execution.
    b. Can I schedule it as a usual SSIS job and have one step check if the job is alreay running. If yes, i can skip the next step, and continue this in a fixed schedule.

    Thanks

    Mac