Protean

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.

(more…)

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. 

(more…)

In my never ending quest to simplify administration tasks on SQL Server, I recently came up with a method to automate the creation of new publications with a one script fits all piece of SQL Code.

Considering that in most enterprises, the default configuration options for replication will remain the same, the only thing that changes are the destination servers and the publications/articles themselves.

(more…)

Ever had a multitude of SQL Agent jobs with dependencies on one another in order for your nightly batch to succeed? Ever had a situation where you forgot to set the steps correctly within the job resulting in the batch failing or not even running at all?

Me too… here’s what I did in order to protect myself from human error.

(more…)

I have just finished the code for a new transactional replication setup.

Requirements were to be able to filter rows according to the destinations that formed part of the fullfilment of the order. This could theoretically include up to 10 destinations however in reality it tended to be 2 or 3. The data would be real-time messages flowing out of the mainframe into the SQL Server via BizTalk.

(more…)