Category: Methodology

SSIS ETL Control (Part 2)

After some prompting, I decided it was about time I posted the solution to my initial post on the subject of metadata driven SSIS.

If you have read part one of this series (or other posts related to replication and sql agent), it will come as no surprise to you to know that I am a big proponent of metadata driven control in SQL Server. Whilst more complex to code, it is infinately easier to manage across environments.

I will add further explanations to this package at a later date, but for now, here is the solution and a very brief summary of what it does.

Amazon Link: Business Metadata: Capturing Enterprise Knowledge

Continue reading “SSIS ETL Control (Part 2)”

SSIS ETL Control (Part 1)

I can’t stress enough the importance of designing the framework for your ETL prior to getting stuck in with individual packages.

I bring this up, because I have seen that look of dread on a developers face (including my own) when they are about to deploy a package to the live environment and aren’t too sure what gremlins are hiding in the live environment that don’t exist in development. Not only this, with the proliferation of SSIS packages across the corporation, it can become extremely difficult to figure out and manage the process flow across all your servers.

It is for this reason, that I am a big fan of metadata controlled ETL. And I’m not just referring to a configuration table set up as part of an SSIS package, I’m talking about modelling a set of tables within a control database that can hold all the data that a package might need to run. I’m also talking about a generic parent package that can use this meta data to control the execution of any packages defined within the tables.

Amazon Link: The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data

Continue reading “SSIS ETL Control (Part 1)”

SSIS Logging in dbo.sysdtslog90

I was recently asked if you can only use the msdb.dbo.sysdtslog90 table to log SSIS package execution…

It is possible to log your SSIS execution to a database other than msdb, simply by scripting the sysdtslog90 table from the msdb database, and creating it in a new db.

Then when you configure your SQL Server log provider, use a connection manager that points to the database on which you’ve just created your table.

Amazon Link: Microsoft SQL Server 2005 Integration Services Step-by-Step