This week my challenge is to come up with a way to make SSIS a dynamic rules-driven engine. This is a concept that is fairly easy to do in a scripting language like C# or T-SQL. It’s not hard to store a bunch of custom code in a table or file, and have it executed either on each row or each table. But – how can I do this in SSIS? SSIS has package variables and the ability to perform conditional logic, but the actual “rule” processing is much harder to create on the fly. And while it’s easy to have SSIS call SQL logic, or even perform script tasks – that’s not always the most efficient use of SSIS.

My customer consolidates external data into a large warehouse and performs business analysis on the resulting data. They need to onboard customers, who will all have unique data formats, without having to redevelop a completely new package and in a timely manner (meaning fast!). They want the process to be focused on SSIS (they just hired an SSIS developer), and they want it operationally sound – meaning it will run all the time without human intervention, monitor itself, and report the quality of the external data.

There is a way to create SSIS packages on the fly using C# or VB.NET code, but that’s going to be a challenge for me since I’m not really a developer in either language. And, I’m not sure the complexity is worth the trade off – the process will be much harder to support.

Impossible you say? Not impossible, but definitely a big challenge. I suspect my final design will be a compromise between fully flexible and easy to support, a mixture of SSIS with some conditional logic and some T-SQL processing. I will be reporting progress on this task over the next few weeks so stay tuned! And if you have any ideas for me, leave me a comment!

Header picture: From the Santa Monica Pier, this picture is a manual blend of two photos with the lights at different places on the ferris wheel. Both shots taken with my Nikon D800, Nikkor 28-300 lens (at 82mm), ISO 100, f/13, 3 seconds. Two images blended using “lighten” mode in Photoshop, and the final result had some minor adjustment to shadows, highlights, and clarity.