Project Description
A set of SSIS frameworks.

This is a set of SSIS templates that are based on the concepts originally published back in Project Real and then extended to accommodate "issues" I've run into building SSIS ETL packages over the years.

There are 9 templates that are set up for loading source to staging, staging to dimension tables (type I and type II), staging to fact tables, and staging to history/persistent staging.

The goal is to use a common approach to SSIS packages that allow rapid deployment and quick troubleshooting. The packages have been annotated with instructions.

The templates are comprehensive and contained in a single BIDS solution. The packages use a table driven approach.

The "package" templates are built for SQL Server 2008 R2 - SP2 (10.50.4000.0) and SQL 2012 - SP1 (11.0.3393.0). The SQL 2012 SSISDB catalog framework was built to SQL 2012 - SP1 (11.0.3393.0). The SQL 2014 "catalog" framework was built to SQL 2014 - RTM (12.0.2000).

The SQL 2008 R2 framework was built with VS2008. The SQL 2012 frameworks were built with VS2010. The SQL 2014 framework was built with VS2013.

There is a HOWTO file in the BIDS solution. The scripts for building the table driven infrastructure are also in the BIDS solution. If you have a good familiarity with SSIS, you should be able to figure out how the packages work in 1/2 an hour. To use most packages, you only need to modify one or two package level variables, possibly repoint one SSIS Configuration setting, enter your source queries, and map the fields. If you're familiar with your sources, a typical package can be created and tested in less than 15 to 30 minutes.

Someone asked me why I didn't use BIML for my templates. I started down that path (I like the BIML concept), but ran into a some challenges that resulted in me just using SSIS packages for templates. Here's the issues I ran into.

1) My packages contain multiple sequence containers that contain other control flow tasks that SSIS views as containers. In BIML you have XML defining containers within containers. This is simple to do in the SSIS GUI, but with BIML this results in complex XML hierarchies. With that level of complexity, if you're using Visual Studio or a third party XML editor, the BIML is difficult to create and mind boggling to extend. It's easy modify the dtsx template packages using the BIDS/SSDT editor. You can get around this by using the Varigence Mist editor, which looks like a great tool, but Mist is really expensive.

2) As I was experimenting creating templates using BIML, I found that BIML doesn't have 100% coverage of all the object properties I wanted to set using SSIS. BIML has really, really broad coverage, but my experience was it wasn't 100%.

3) BIML gives you great cross version compatibility. That's important if you're a consultant and don't know which version will be working on next week. But most organizations I've been at only require forward compatibility. If they are running SQL 2008 R2, they aren't moving back to SQL 2008, they are either staying with SQL 2008 R2 or moving forward to SQL 2012. When that's the case, BIDS/SSDT will upgrade the template packages.

4) BIML requires BIDS Helper (great tool). The issue is BIDS Helper only works with the version of BIDS/SSDT that was released with the SQL Server package. If you wanted to upgrade your tools to the new Visual Studio 2012 based SSDT, you can't use BIDS Helper and BIML.

Last edited Nov 3, 2014 at 8:04 PM by sqlarchitect, version 8