SSIS ETL Framework


In my current job, we have a custom ETL framework to control the execution of SSIS packages.

Some of the features of our ETL framework is as follows:

a) Control the execution of packages using metadata tables

We use a manager package to call other packages which in turn call other packages as shown below.

ManagerPackage (Level 1)

ExtractManager (Level 2)

ExtractOne (Level 3)

ExtractTwo (Level 3)

DataLoadManager (Level 2)

TransformAndLoadOne (Level 3)

TransformAndLoadTwo (Level 3)

If we want to run a snapshot every day, week or month – we can set the frequency and the snapshot packages would run based on the settings.

b) Detailed logging of the execution times and success or failure status.

c) Rerun a component if it failed in the previous run

d) Ability to rerun Data Load for Snapshot tables using control tables

e) Audit information detailing how many records are loaded in every LoadRun.

When I googled to see if there are any free / open source frameworks available I found this.

This provides metadata tables, SSIS Packages (Manager and sample child packages), SSRS Reports to measure performance of the package execution.

– Jegan


About EasyBIJegan

Business Intelligence Developer
This entry was posted in BusinessIntelligence, DataWareHousing, SSIS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s