Unit testing SSIS 2008 expressions

In Microsoft SQL Server 2008 Integration Services, you can use expressions, see (Integration Services Expression Concepts, http://technet.microsoft.com/en-us/library/ms141827.aspx) in your dataflow tasks.

For instance, if you want to import a flat file containing 2 columns: FirstName and LastName and your destination table contains only one column: Name, then you can concatenate the two columns by using a SSIS expression: @FirstName + @LastName, this is a very simple example, but when you are creating larger expressions like:

((DATEDIFF("ss",(DT_DBTIMESTAMP)(SUBSTRING(DatumVoormelding,5,2) + "/" + SUBSTRING(DatumVoormelding,7,2) + "/" + SUBSTRING(DatumVoormelding,1,4) + " " + SUBSTRING(TijdstipVoormelding,1,2) + ":" + SUBSTRING(TijdstipVoormelding,3,2) + ":" + SUBSTRING(TijdstipVoormelding,5,2)),(DT_DBTIMESTAMP)(SUBSTRING(DatumSorteerbeslissing,5,2) + "/" + SUBSTRING(DatumSorteerbeslissing,7,2) + "/" + SUBSTRING(DatumSorteerbeslissing,1,4) + " " + SUBSTRING(TijdstipSorteerbeslissing,1,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,3,2) + ":" + SUBSTRING(TijdstipSorteerbeslissing,5,2)))) > @[User::MinTijdsduurVoorgemeld]) ? 1 : 0

things can get more complicated and then you might want to use a unit test, to test this specific expression.

There are several ways to unit test, a SSIS expression:

  • Converting the SSIS expression to a SSIS Script Task, some calculation can’t be done with SSIS expressions, then you can use a SSIS Script task, this task can be written in C# and the C# code can be unit tested in Visual Studio.
  • Adding a second SSIS project to your solution, that contains SSIS dataflow task for every unit test

If you just want to manually test the expression without running the complete package, you can create a second SSIS Project to your solution and use flat file source, destination and dataflow tasks to unit test only the SSIS expression. In the screen dumps I added a second SSIS project named: Tpp.Miss.Etl.UnitTest



The UnitTest_Voorgemeld_Input.txt contains all the input data needed to unit test the SSIS expression:


Data flow task
The data flow task contains:

  • Flat File Source (UnitTest_Voorgemeld_Input)
  • Derived Column (contains the SSIS expression)
  • Flat File Destination (UnitTest_Voorgemeld_Output)



Flat File Source



Derived Column (contains the SSIS expression)

Because the Derived column is “Added as a new column”, we must add this new column to the output flat file connection and map the derived column to the output column, to store the expression result in the unit test output file.


Flat File Destination

The flat file destination, contains one extra column: Voorgemeld


Extra column must be mapped:





The UnitTest_Voorgemeld_Output.txt contains all the unit test output, after running the unit test package the result will be:


In the file UnitTest_Voorgemeld_Output.txt:


By storing the unit test data en the unit test package in the same solution as the SSIS project that uses the expression, our continuous integration server can run the complete packages and the individual unit tests in the nightly builds.






Leave a Reply

Your email address will not be published. Required fields are marked *