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

 

image

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

 

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)

 

image

Flat File Source

image

 

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.

image

Flat File Destination

The flat file destination, contains one extra column: Voorgemeld

image

Extra column must be mapped:

image

 

 

UnitTest_Voorgemeld_Output.txt

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

image

In the file UnitTest_Voorgemeld_Output.txt:
20100521,083030,20100521,084030,20100521083030
20100521,,20100521,084030,20100521
20100521,083030,20100521,,20100521083030

 

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 *