Search code examples
sql-servervisual-studiossispackagedata-warehouse

Linking SSIS packages to a table to all me to control which packages should run

I am a newbie to SSIS and trying to replicate the current ELT process (which is configured via a set of store procedures) using SSIS.

The ELT process: I have a config table that has all the table information stored. The key column in this table is called 'Active'. This allows me to enable and deactivate tables that I don't want the ELT to run at any given time. The ELT_SP scans the DWH_Process table for tables marked as active and thereafter executes the schedule for that specific job via server agent.

The SSIS Process I have configured the SSIS packages to extract data from the CRMs and import into DWH. I have used the 'sequence containers' control flow to replicate the ELT process, and this is working perfectly.

The problem is, I would then have to update 300+ tables and redeploy to the DWH.

In SSIS, what other options are available to me, and is there a simpler way to add in the 'check active status' before executing the package through a job agent?

enter image description here

Solution

  • A setup I've used in the past is something like this: Concept

    1. Setup a table with the configuration, something with columns like PackageName, IsEnabled, Partition ...
    2. Create a loop package, that you are going to call in the root packages. The loop package queries, the config table based on a parameter of the package (e.g. Partition=1) , for a specific partition. The query also filters on IsEnabled = 1 or a different condition for your case. After the query you starts a foreach loop for every result/package that's returned from the query. This foreach then calls the actual package with your logic. You use a variable from the foreach to start the Execute package task
      Loop packages
    3. Create a root package
    4. In a sequence container in the root package, add N execute package tasks that calls the loop package, where you pass a parameter which partition they are executing. (This is purely for performance to run multiple packages at once.)
      Sequence container with execute package tasks

    The benefit of this approach is you can enabled disable packages with a simple update statement, no need to redeploy. You can play with the partitions to get the best performance.