Building an SSIS Testing Framework

If you compared the Google results for “testing c#” and “testing SSIS” you would quickly realize that testability isn’t one of the strengths of SSIS. I’ve seen a few different frameworks which were either too complicated or didn’t really work, in this post I’m going to give you my take on building an SSIS Testing Framework.

The Goal

The overall goal of what I wanted to achieve from my test framework:

• Simplicity – writing tests needs to be as simple and easy as possible
• The ability to load sample data into database tables
• The ability to run a “setup” sql script
• The ability to run an assert script to determine if the test has passed/failed
• Auditing/history of previous test runs

My Solution

I didn’t initially set out to build a testing framework, it just evolved into one over the course of the couple of days I was working on it. I started off by manually testing my SSIS packages and realized I was following the same steps over and over again:

1. Load sample data
2. Run SSIS Package
3. Run some scripts to check if it worked

I could see that this was going to get repetitive so started to think about how to automate it. After a few iterations this is what I’ve ended up with (at time of writing).

The TestController package follows the well trodden AAA (assemble, act, assert) approach that is found among the TDD (test driven development) practitioners in the software development industry.

This package does the following:

1. Gets a list of tests to execute
2. Then in the foreach loop does the following for each of the test cases:

Assemble:

1. A script task is used to get a bunch of test parameters from a SQL table, then updates the associated variables in the TestController package, at the moment these are only used to control which tables have data loaded
2. A pre-data load setup SQL script is then executed
3. Load Sample Data will then load any sample data for this test run
4. A post data load setup SQL script is then executed

Act:

1. Execute the package under test (PUT)

Assert:

1. Run an assert SQL script

The Downsides

The solution isn’t perfect, here are a few of the problems that it has at the moment:

• Unable to pass package parameters to the package under test in the Act phase
• Makes assumptions about how you want to do your setup routine
• Is purely focused on packages that touch the database
• Writing assertions isn’t as easy as I would like, it involves writing lots of T-SQL and copy/pasting code throughout the different assert scripts
• All your databases need to be on the same server
• Maintaining the variables and and connection managers is painful

Some of these I’m not terribly bothered about, for example having to have all of your databases on a single server I think is acceptable as this is for testing purposes. Whereas the duplication of assertion code could be easily fixed by creating some reusable stored procedures (e.g. Assert.TableEqual, Assert.TableEmpty etc.) similar to those offered by tSQLt.

One of the more painful areas of the framework is setting up tables to have sample data loaded, the process involves adding variables, setting up connection managers adding components to the data flow task and updating the script component. One way of fixing this issue that I thought of was generating the TestController package with BIML and have all of this automatically generated.

Wrap Up

As a starting point I’m really happy with how the TestController is working at the moment. During the development of it and the use afterwards I picked up a few bugs with the packages that I wanted to test, so it is paying for itself already :)

This post was first published on Ken's Blog