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:
-
Using C# and the SSIS object model: http://www.codeproject.com/KB/database/Digging_SSIS_object_model.aspx
-
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
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)
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:
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:
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.