What is SSIS Tester and how it helps me?

SSIS Tester is a testing framework built on top of SQL Server Integration Services. It enables you to test packages, tasks and precedence constraints. It supports two types of tests: 1) unit and 2) integration tests.

SSIS Tester helps you to develop you ETL process in the test driven manner and to identify errors early in the development process.

Take a look at introductory presentation.

Read the MSDN article about SSIS testing.

What are advantages of using SSIS Tester?

Read about TDD – Test Driven Development

What SSIS Tester installation package contains?

SSIS Tester comes as the Visual Studio project template. A compile-ready demo project with sample packages and database is available together with the test templates and integrated Visual Studio help. A tool that can generate test templates from existing packages is also included. The tool can generate MS Test project from existing tests so you can integrate them into build process. These tests can be run with ReSharper as well.

What are basic requirements for SSIS Tester?

In order to use SSIS Tester you will need a) SQL Server 2008/2012 with locally installed integration services b) .NET framework 3.5 and c) Testing Tools for Visual Studio 2008/2010.

There are two versions of SSIS Tester that target different SQL Server versions. One is for the SQL Server 2008 and the other for SQL Server 2012 version. If you install version for SQL Server 2008, you can use both Visual Studio 2008 and 2010 to create tests. If you install version for SQL Server 2012 you can only use Visual Studio 2010.

What kinds of tests can it perform?

Unit and live tests are supported by the framework.

Unit tests are performed in 3 steps that enable you to prepare and verify tests, and to do cleanup after the test has executed. Unit tests are designed to test packages or tasks for a specific use cases, therefore you can write more than one unit test to test different aspects of the same package or task. With a unit test you can test particular package, task or precedence constraint.

It is challenging to create realistic test setup and initial conditions needed for a unit test so the package or task being tested behaves like part of the complete ETL process. To overcome this problem SSIS Tester supports integration tests - also called live tests. Live test is basically a post-condition, defined for a package/task that needs to be satisfied after the package/task has executed. This post-condition corresponds to the verification step of a unit test. Unlike the unit test, live test can not be explicitly defined for a precedence constraint, but they can be tested in a live test as well. They are different from the unit tests in a way that you do not have possibility to prepare the test prior to package execution and perform clean up after it. Similarly as when writing unit test, you have to verify the execution result of a package or a specific task. Another big difference is that, unlike unit test, integration test does not execute the package; it is the other way round, a package executes a test when it comes to the point for which a post-condition is defined. You should use live tests to test you whole ETL process.

What elements of package can be tested?

Packages as a whole, tasks and precedence constraints can be tested. The structure of the test stays the same, whether package, task or precedence constraint is tested. The only difference are the annotations used to write tests; when testing a specific task you need to explicitly annotate your test class with that task name. In both cases you can access package and task runtime data.

Can it be used with existing packages?

There is no need to adjust your existing packages in any way so that they can be tested with SSIS Tester.

How to create a unit test?

Take a look at tutorials.

How to create an integration test?

Take a look at tutorials.

Are there any samples available?

Demo project is included in the Visual Studio project template. You can use it to learn about different aspects of SSIS Tester by debugging sample unit and integration tests. Additionally you can see tutorials available on this web site.

How is the test result presented?

A GUI tool is available so you can monitor tests during the execution. You can export test results to HTML as well. This is convenient if you want to mail results or save them for later review. Additionally, you can use MS Test project or ReSharper to execute and monitor the tests.

Can I debug broken tests?

Yes. Both unit and integration tests can be debugged. Just set breakpoints at wanted location in a test and start your test project in Visual Studio debugger.