locked
SSIS dynamic execution times RRS feed

  • Question

  • First a brief background. I have one master package that calls 3 child packages (load to staging, load to DW, process cube).

    These 4 packages are stored on the Integration Services MSDB and a SQL Agent job calls the master integration package every 6 hours.

    My requirement is to execute the load to staging package every 6 hours (as is happening now) and then the full master package every 24 hours. (so the load to DW and Cube is only executed once a day)

    The problem I face, is I have to have only 1 SQL Agent  job, calling 1 package (the master package)

    So I need some kind of logic that for the first 3 executions of the job, only execute the load to staging package and then on the 4<sup>th</sup> execution load all 3 packages (staging, DW,Cube).

    I was thinking of two options, but I don’t like either… so any help would be great

    Option 1. Store a counter in a SQL table, ever time the job executes call this counter in a package variable and then depending on the value (either 1,2,3,4) load only staging or load the full process). The counter is reset after 4 executions

    Option 2. When executing the job, the master package checks for the time, if between a certain time execute the full process, otherwise only execute load to staging.

    If you need any further info, let me know.

    Thanks

    Friday, August 16, 2013 3:14 PM

Answers

  • Option 1 is preferred due to being not dependent on the time.

    You can achieve the execution flow control with the precedence constraints.

    An overview what and how you can use them is here.


    Arthur My Blog

    • Proposed as answer by Mike Yin Tuesday, August 20, 2013 2:28 PM
    • Marked as answer by kiwiNspain Tuesday, August 20, 2013 3:38 PM
    Friday, August 16, 2013 3:38 PM
  • yes, your option 1 is feasible.

    1. Have SQL control table. Each time the step 1 (package 1) is executed, it should update the value in the table from 1-->2--->3--->4--->1 on each execution.
    2. Also in the master package, have a exec sql task to read the column value from SQL control table and save the result into variable, vCount.
    3. In the master package, have precedence constraint with expression in between package 1 and package 2. There will be a simple expreesion
    @vCount==4

    When this happens, control flow is passed to the subsequent packages and they run only on 4 th exec.

    Thanks, hsbal

    • Proposed as answer by Mike Yin Tuesday, August 20, 2013 2:28 PM
    • Marked as answer by kiwiNspain Tuesday, August 20, 2013 3:38 PM
    Friday, August 16, 2013 5:59 PM

All replies

  • Option 1 is preferred due to being not dependent on the time.

    You can achieve the execution flow control with the precedence constraints.

    An overview what and how you can use them is here.


    Arthur My Blog

    • Proposed as answer by Mike Yin Tuesday, August 20, 2013 2:28 PM
    • Marked as answer by kiwiNspain Tuesday, August 20, 2013 3:38 PM
    Friday, August 16, 2013 3:38 PM
  • yes, your option 1 is feasible.

    1. Have SQL control table. Each time the step 1 (package 1) is executed, it should update the value in the table from 1-->2--->3--->4--->1 on each execution.
    2. Also in the master package, have a exec sql task to read the column value from SQL control table and save the result into variable, vCount.
    3. In the master package, have precedence constraint with expression in between package 1 and package 2. There will be a simple expreesion
    @vCount==4

    When this happens, control flow is passed to the subsequent packages and they run only on 4 th exec.

    Thanks, hsbal

    • Proposed as answer by Mike Yin Tuesday, August 20, 2013 2:28 PM
    • Marked as answer by kiwiNspain Tuesday, August 20, 2013 3:38 PM
    Friday, August 16, 2013 5:59 PM