none
Running SSIS packages In Parallel To Optimize RRS feed

  • Question


  • Hi, 

    I currently have a Master-Child Packages set up :

    1. A Excel CSV file which contains package order, package name and package location

    2. I import the CSV file data into the SQL server table and then using the ForEachLoopContainer in Master Package- Execute the child package - using Execute package tasks.

    Since I have around 80 child packages, I would want some of the packages which dont have any dependencies to run in parallel and others which do wait for the dependent package to finish and then execute. 

    Using the set up that I currently have, please advice possible ways to execute my child packages in  parallel. Currently per my setup all my packages execute one after the other which is not an optimal solution.

    Please advice,

    Thanks 


    EVA05

    Sunday, August 5, 2012 4:00 AM

Answers

  • Knocked up a solution where I load all the filename/ package name into a sql table FileList. Each of the row is divided into 4 batches and given a groupid. Within each group you give the sortid which defines the order of package execution/ dependency. Then we have 4 threads executing the packages parallely like below.

    Use this script in update FileList

    ;WITH FPG AS
    (
    SELECT
     ID,
     CASE WHEN
      groupid > 0 THEN CONVERT(TINYINT, (Dense_Rank() OVER (ORDER BY  GROUPID) - 1) % ?  + 1)
     ELSE
       CONVERT(TINYINT, (ROW_NUMBER() OVER (ORDER BY  ID) - 1) % ?  + 1)
     END as PG
    FROM [FileList])
    --Update Table /ParallelGroupID  
    UPDATE  [FileList]
    SET  ParallelGroupID = FPG.PG
    FROM [FileList] f JOIN FPG
     ON F.ID  = FPG.ID;

    Use this script in each sql task before each FEL container

    SELECT FileName
     ,ParallelGroupID
     ,GroupID
     ,SortID
     ,ExtractLoadPackageName
            ,FilePath 
    FROM
     FileList
    WHERE  ParallelGroupID = <1/2/3/4>
    ORDER BY GroupID,SortID


    http://btsbee.wordpress.com/

    • Marked as answer by eva05 Monday, August 6, 2012 5:30 PM
    Monday, August 6, 2012 9:57 AM

All replies

  • I think it is better to place your dependent packages in a Sequence Container, set precedence constraint to connect to another sequence container which has dependencies and set the FailParentOnFailure property to True for each package.

    Cheers


    MCP, MCTS, MCITP

    • Proposed as answer by DotNetMonster Sunday, August 5, 2012 10:49 AM
    Sunday, August 5, 2012 5:28 AM
  • In that case I lose the Master-Child package setup. I have one master package which will execute all the child packages in foreachloopcontainer using the execute package tasks. 

    Is there a way we can dynamically build the master package in such a way that I can provide the packagename, packageorder, packagedependencies ( which packages are must complete before executing current one) in a file and info from file dynamically builds a master package.

    In this way however many changes and/or additions I make , I would just need to modify this one file and rest is taken care in ETL itself. 

    I guess my setup- foreachloopcontainer to execute child packages won't work. but was thinking if there is any possible way to auto generate several execute package tasks with precedence based on the values in the file ?

    Please advice,

    Thanks 


    EVA05

    Sunday, August 5, 2012 12:56 PM
  • you can use a foreach loop container, but note that this will not consider order of execution or package dependency. but you can create table(s) structure for storing child packages information and their execution order and other details. then select data from those tables ordered by executionOrder, and load them into an object type variable inside ssis package. then with Foreach Loop container loops through the ado in the object variable. and finally put an execute package task inside the foreach loop container to pass path of each child package to the execute package task and execute it.


    http://www.rad.pasfu.com

    Sunday, August 5, 2012 9:02 PM
    Moderator
  • Make use of a table and set the dependencies of the packages in the table and use aforeach loop continer to llop through records in the table and call the package
    Monday, August 6, 2012 8:51 AM
  • Knocked up a solution where I load all the filename/ package name into a sql table FileList. Each of the row is divided into 4 batches and given a groupid. Within each group you give the sortid which defines the order of package execution/ dependency. Then we have 4 threads executing the packages parallely like below.

    Use this script in update FileList

    ;WITH FPG AS
    (
    SELECT
     ID,
     CASE WHEN
      groupid > 0 THEN CONVERT(TINYINT, (Dense_Rank() OVER (ORDER BY  GROUPID) - 1) % ?  + 1)
     ELSE
       CONVERT(TINYINT, (ROW_NUMBER() OVER (ORDER BY  ID) - 1) % ?  + 1)
     END as PG
    FROM [FileList])
    --Update Table /ParallelGroupID  
    UPDATE  [FileList]
    SET  ParallelGroupID = FPG.PG
    FROM [FileList] f JOIN FPG
     ON F.ID  = FPG.ID;

    Use this script in each sql task before each FEL container

    SELECT FileName
     ,ParallelGroupID
     ,GroupID
     ,SortID
     ,ExtractLoadPackageName
            ,FilePath 
    FROM
     FileList
    WHERE  ParallelGroupID = <1/2/3/4>
    ORDER BY GroupID,SortID


    http://btsbee.wordpress.com/

    • Marked as answer by eva05 Monday, August 6, 2012 5:30 PM
    Monday, August 6, 2012 9:57 AM