How-to test a project that is focused on SSIS

How-to test a project that is focused on SSIS
Recently I have been working in a project where SSIS is the primary technology used for solve a business case where focus lies on data-processing. Normally I work with plain C# code and I like test driven development and I like the bi-product that comes with TDD – the unit tests and in the longer run – the integration tests. In this little article I will go through the steps needed to setup a fairly simple but yet effective testing solution for SSIS-development.

All C# code shall be placed in a separate assembly and hosted in GAC
First of all: “All C# code that is written I script-components and script-tasks etc. should lie in a separate C#-assembly and should be consumed via the global assembly cache (GAC).” The C# code contained in the SSIS-packages should only place calls to C# code in the external assembly/assemblies. By taking this design decision, you could easily write tests for your code.

Load and run your packages via code
Ok, that was the obvious decision, now what could be done to test our packages? We need a way to execute our packages and test the outcome. The first step is to configure the packages to be deployed to a certain directory whenever the Integration services project is built. This is achieved by tweaking the “Deployment utility settings” under properties of the SSIS-project. Activate it by setting “CreateDeploymentUtility” to true and to specify a path for “DeploymentOutputPath”.

In our integration test project we kicked the packages using simple “load and run technique” which you could read up on here (http://msdn.microsoft.com/en-us/library/ms403355.aspx).

Make the package-execution configurable
For ease of setting up different environments (like different developer machines or build servers) I made a Package-executor that was configurable from the app-config.

<ssisPackageExecutor
  xmlConfigFile="C:\ProjectX\SSIS-Config\CommonConfig-Test.dtsConfig">
  <packages>
    <package name="PackageOne"
             locationType="Filesystem"
             packagelocation="C:\ProjectX\Packages\PackageOne.dtsx" />
    <package name="PackageTwo"
             locationType="Filesystem"
             packagelocation="C:\ProjectX\Packages\PackageTwo.dtsx" />
  </packages>
</ssisPackageExecutor>

The attribute “xmlConfigFile” above is used by the Package-executor to inject configurations that are valid for testing purposes. E.g: Point a connection-string to a specific Test-database.

All that was left was to make a simple SSIS-assert class (which uses the Package-executor) on which you could call, e.g:

SSISAssert.SuccessfulPackage("PackageOne");
SSISAssert.FailedPackage("PackageTwo");

After the execution of the package you “just” have to write tests that check the database, filesystem etc. for correct changes.

Setting up a database automatically
To make the tests run smoothly you need to have the Test-database setup automatically. One way to achieve this is to make use of script-files that are executed on a certain database. To achieve this all tables, views, functions, stored-procedures etc. needs to be scripted and placed on a disk. I made a simple database installer that checks for version-folders in a certain script-folder repository. If a certain folder should be installed/executed and in what order is configurable in the app-config (see below for example). So the script-folder contained subfolders where each subfolder represented a certain version of the database. For testing purposes I added a test-version folder that contained scripts necessary for setting up the Test-database with specific test tables. Theses specific test-scripts where not executed/installed in the Normal-database. The custom database installer that I build was configurable so that you could install several databases at the same time. This made it easy for each developer to have a Test-database and a Normal-database on their machines.
The database installer made use of Sql-Server Management Objects (SMO) to execute the scripts and the Test-database was initialized once per assembly. If a certain test required certain test-data in the database, initialize and cleanup methods (using attributes [TestInitialize] and [TestCleanup]) in the test-class called stored-procedure (which was installed/created by a script in the test-version-folder); one for initialization of test-data for the specific test and one for clean up.
The Configuration looked like this:

<databaseInstaller>
  <setups>
    <setupInfo name="Normal"
               connectionStringName="NormalDb"
               scriptFolderPath="C:\ProjectX\Scripts"
               versions="initialize;v1"/>
    <setupInfo name="Test"
               connectionStringName="TestDb"
               scriptFolderPath="C:\ProjectX\Scripts"
               versions="initialize;v1;vTest"/>
    </setups>
</databaseInstaller>

The script-version-folders are applied in the order that they are listed above in the “versions” attribute. If a folder is not present in the attribute but on disk, it will not be executed/installed.

That’s it. Happy testing.

//Daniel