SSIS – Unit Testing – An Approach

28 09 2009

What is Unit Testing?

In computer programming, unit testing is a software verification and validation method in which a programmer tests if individual units of source code are fit for use. A unit is the smallest testable part of an application. In procedural programming a unit may be an individual program, function, procedure, etc., while in object-oriented programming, the smallest unit is a class, which may belong to a base/super class, abstract class or derived/child class.

Ideally, each test case is independent from the others: substitutes like method stubs, mock objects, fakes and test harnesses can be used to assist testing a module in isolation. Unit tests are typically written and run by software developers to ensure that code meets its design and behaves as intended. Its implementation can vary from being very manual (pencil and paper) to being formalized as part of build automation.

SSIS Unit Testing a Nightmare?

I have always been displeased being not able to unit test my packages created in SSIS. This was because there was no way to do this. SSIS Data flows can be really complex. Worse, you really can’t execute portions of a single data flow separately and get meaningful results.

Further, one of the key features of SSIS is the fact that the built-in data flow toolbox items can be equated to framework functionality. There’s not so much value in unit testing the framework.

Below listed are the some of the ways how I have done unit testing in the past with SSIS packages.

  1. Create our own frame work in C#.
  2. Access SSIS package activities and variable through .NET application

I would like to give the brief knowledge of the above mentioned ways below.

Create our own framework in C#

Meaningful unit testing of SSIS packages really comes down to testing of Executables in a control flow, and particularly executables with a high degree of programmability. The two most significant control flow executable types are Script Task executables and Data Flow executables.

Ultimately, the solution to SSIS unit testing becomes package execution automation.

There are a certain number of things you have to do before you can start writing C# to test your scripts and data flows, though. I’ll go through my experience with it, so far.

In order to automate SSIS package execution for unit testing, you must have Visual Studio 2005 (or greater) with the language of your choice installed (I chose C#).

Interestingly, while you can develop and debug SSIS in the Business Intelligence Development System (BIDS, a subset of Visual Studio), you cannot execute SSIS packages from C# without SQL Server 2005 Developer or Enterprise edition installed (“go Microsoft!”).

Another important caveat… you CAN have your unit test project in the same solution as your SSIS project. Due to over-excessive design time validation of SSIS packages, you can’t effectively execute the SSIS packages from your unit test code if you have the SSIS project loaded at the same time. I’ve found that the only way I can safely run my unit tests is to “Unload Project” on the SSIS project before attempting to execute the unit test host app. Even then, Visual Studio occassionally holds locks on files that force me to close and re-open Visual Studio in order to release them.

Anyway, I chose to use a console application as the host app. There’s some info out there on the ‘net about how to configure a .config file borrowing from dtexec.exe.config, the SSIS command line utility, but I didn’t see anything special in there that I had to include.

The only reference you need to add to your project is a ref to Microsoft.SqlServer.ManagedDTS. The core namespace you’ll need is

using Microsoft.SqlServer.Dts.Runtime;

In my first case, most of my unit testing is variations on a single input file. The package validates the input and produces three outputs: a table that contains source records which have passed validation, a flat output file that contains source records that failed validation, and a target table that contains transformed results.

What I ended up doing was creating a very small framework that allowed me to declare a test and some metadata about it. The metadata associates a group of resources that include a test input, and the three baseline outputs by a common URN. Once I have my input and baselines established, I can circumvent downloading the “real” source file, inject my test source into the process, and compare the results with my baselines.

Access SSIS package activities and variable through .NET application

We need to set variables defined at SSIS package through .Net Application. In this .Net application, we can get list of SSIS packages and execute them manually. For the testing of SSIS packages we need more control on the package like disable specific task in the package, setting some runtime variable in the SSIS package. All these things can be achieved with the help of .Net application.

There are two ways to store the SSIS packages in the server. One way of storing is in the file system and the other way of storing these packages are in the MSDB database of SQL Server. If we want to test our packages we need to store those packages in the MSDB database. We can access those packages stored in MSDB with the help of the below .Net assemblies








Load all the packages from the SQL Server MSDB database into your array variable.

Loop through all the packages, select the specific package and get the properties of that package and do the necessary validation with the help of the methods and properties of the .Net assemblies.

 For performing the entirefunctionality user must have respective permission on MSDBdatabase.

ssisUnit – Dark days are gone

It seems that those days of darkness are gone. SQL Server Integration Services
Community has felt this need of the day and has come up with a Unit Testing
Framework. Now guyz! if you are not from development background even then
you don’t need to worry because no .net code has to be written to write
your unit tests. All you have to create is an XML files with your commands.
So you don’t need to know any .net code. Still I have not found out if I
want to write some .net code then how can I do that.

The thing I liked most is that this Unit Test framework is based on xUnit.
It means it follows the same setUP, test and teardown flow. There is also
support for Test Suites. The developers of Microsoft Dynamics AX should be
happy to listen to this news. To keep ease in consideration, a GUI has been added to create Unit Tests in ssisUnit.

You can download the free version of ssisUnit from the following location.

What is xUnit

As we have mentioned above that ssisUnit is based on xUnit family. Let me give some information on xUnit for better understanding of ssisUnit.

Various code-driven testing frameworks have come to be known collectively as xUnit. These frameworks allow testing of different elements (units) of software, such as functions and classes. The main advantage of xUnit frameworks is that they provide an automated solution with no need to write the same tests many times, and no need to remember what should be the result of each test. Such frameworks are based on a design by Kent Beck, originally implemented for SmallTalk as SUnit, but are now available for many programming languages and development platforms.

The overall design of xUnit frameworks depends on several components.

Test Fixtures

A test fixture (also known as a test context) is the set of preconditions or state needed for a test to succeed. The developer should set up a known good state before the tests, and after the tests return to the original state.

Test Suites

A test suite is a set of tests that all share the same fixture. The order of the test shouldn’t matter.

Test Execution

The execution of an individual unit test proceeds as follows:

Setup(); /* First, we should prepare our ‘world’ to make an isolated environment for testing */

/* Body of test – Here we make all the tests */

teardown(); /* In the end, whether succeed or fail we should clean up our ‘world’ to not disturb other tests or code */

The setup() and teardown() methods serve to initialize and clean up test fixtures.


An assertion is a function or macro that verifies the behavior (or the state) of the unit under test. Failure of an assertion typically throws an exception, aborting the execution of the current test.

Please give your suggestions/comments on this post……………