SSIS Unit Testing

Andrew Smith Posted on

Testing of SSIS packages can be an ah-hoc, manual process that’s performed only at the package level. I.e. checking that running a whole package (or set of packages) produces the expected data.

This approach will likely not test all paths through an ETL, as well as failing to thoroughly test priority fixes that urgently need to go into production.

John Welch, in his talk on Practical Unit Testing for SSIS Packages at SQLBits XII, presents a unit testing framework called ssisUnit that he developed for SSIS. This post highlights some of the key points from his session, as well as documenting my experience with installing and using ssisUnit with SSIS on SQL Server 2014.

Unlike the ad-hoc manual approach to testing, ssisUnit can be automated, and results in a process that John explains is:

(1) Fast
(2) Repeatable
(3) Verifiable

and where this replaces the “deploy and hope” approach to ETL deployment with a healthy degree of confidence instead.

ssisUnit testing is also performed at the SSIS task level, and so is at a lower granularity than testing that only considers the effects of a whole package. ssisUnit is based upon the well established xUnit framework that resulted from work by Kent Beck on Smalltalk in 1998. An individual ssisUnit/xUnit test has this structure:

(1) Setup (put all the pre-conditions in place)
(2) Test (execute the code and then check assertions)
(3) Teardown (undo any changes made by the test)

This results in individual unit tests that are atomic and independent of other tests.

Installation

ssisUnit can be found on Codeplex at https://ssisunit.codeplex.com/. The official release is for SQL Server 2008, but there’s also a beta download for SQL Server 2012 and an alpha download for SQL Server 2014. I tried the 2012 version first since it looked more stable, but it could not find the SSIS .NET assemblies it needed on my SQL Server 2014 installation. I then replaced this with the SQL Server 2014 version, which did run successfully, albeit only after I’d edited the ssisUnitTestRunner2014.exe.config file to include <startup useLegacyV2RuntimeActivationPolicy=”true”> to overcome a runtime error. The installation took place on my Windows 10 laptop.

From CodePlex it appears that no releases have been done to this open source project within the last 2 years, possibly because Pragmatic Works have included ssisUnit within their BI xPress product, so maybe that’s where the ongoing development is taking place. That said, from below it can be seen that even this alpha version for SQL Server 2014 is functional and possibly of great use.

There are 2 executables that implement ssisUnit, which can be run from the command line and/or added to the SSDT Tools menu:

ssdttools

They were added to this menu by entering their names and file system paths via the “External Tools…” menu item shown above.

Setup

I evaluated the product using the Product Sample Package and Test zip file supplied on CodePlex. After adding the test package to a new solution in SSDT, and editing the Connection Managers to have file paths and connection strings relevant to my PC, it looked like this:

ssisUnitPackage

I then added the XML file containing sample unit tests for the package to the SSDT Solution Explorer:

ssdttoolsse

and edited this to have the same file paths and connection strings used by the package.

The ssisUnit File Structure

The XML file used to specify the tests begins with the following sections:

ssisTestXML1

These specify any connection managers required by ssisUnit and also the package(s) under consideration. Following this are optional XML sections that can be used to specify any initial setting up for the test suite as a whole. Then come the specification of the tests themselves. An example is:

ssisTestXML2

So this unit tests the “Load Products” Data Flow Task in the control flow shown previously. The setup stage copies a .CSV file into place that will be loaded by the data flow, and creates the target table if it doesn’t already exist. The Data Flow Task is then executed, and an assertion is run that checks that the data flow results in 6 rows being loaded into the target table. (As an alternative to row counts, ssisUnit also allows for the contents of the target table to be compared with the contents of a test table with the known correct contents.)

No tear down section exists at the end of this test, but one can be included if required. Following all the test sections, a final tear down section can also be included, which specifies anything that needs undoing for the test suite as a whole.

Running the Tests

If the “SSISUnit” item is executed from the SSDT Tools menu whilst highlighting the desired test file in Solution Explorer, the following appears in the Output window (or alternatively it can be sent to a command prompt window):

ssisTestXML3

This shows that the “Load Products” test referred to previously has passed, as well as the test following it. A lot of data appears in this window and it’s not easy to see quickly what’s passed and what’s failed. As an alternative the “SSISUnit UI” item can be executed from the SSDT Tools menu. For the same test file, this produces a GUI interface:

ssisTestXML4

This can be used to build the XML test file to begin with (as opposed to manual coding), but here it’s just used to run the test. The last few lines of its test results are shown in the GUI here:

ssisTestXML5

So the “Passed” column to the right greatly eases viewing of the results, as does the final summary row of the test output, and also the information in the GUI status bar.

Summary

Whilst not a polished product – the alpha version for SQL Server 2014 does have a couple of obvious GUI bugs – ssisUnit still looks impressive at first sight and has clearly been very well thought through. The fact that it is free of charge does mitigate some of the minor niggles also.

There’s no doubt that writing tests for most or all of the tasks in a package or set of packages will take some time, but that’s no criticism of this particular framework – this will always be the case. The big advantage here is that the end result is a test harness that can be run repeatedly and automatically, and that produces an unambiguous pass/fail result.

ssisUnit can also be included within a continuous integration approach to developing and building SSIS packages, since it can be called as an executable from any scripting environment, and where the results of automatically run tests can be made available for human or machine parsing.

Testing is performed at the task level only – it’s not possible to test each transform individually within a data flow. This doesn’t have to be a bad thing though, as it will help keep data flows minimal and focused (as far as is practical).

And as John Welch states, developers really need their own copy of the source database to test with, since tests can’t be run reliably if there’s a chance that other people or other programs are changing the data that’s being tested against.

All in all though, it looks to be a big step in the right direction for moving from ad-hoc, manual testing to more thorough automated testing.