Unit testing data flow in a ssis package

Question

Is there a way to unit test data flow in a ssis package .

Ex: testing the sort - verify that the sort is porperly done.

Answer 1

There is an unit testing framework for SSIS - see SSISUnit.

This is worth looking at but it may not solve your problem. It is possible to unit test individual components at the control flow level using this framework, but it is not possible to isolate and individual Data Flow Transformations - you can only test the whole Data Flow component.

One approach you could take is to redesign your package and break down your DataFlow component into multiple DataFlow components that can be individually tested. However that will impact the performance of your package, because you will have to persist the data somewhere in between each data flow task.

You can also adopt this approach by using NUnit or a similar framework, using the SSIS api to load a package and execute an individual task.

Answer 2

Short answer - not easily. Longer answer: yes, but you'll need lots of external tools to do it. One potential test would be to take a small sample of the data set, run it through your sort, and dump to an excel file. Take the same data set, copy it to an excel spreadsheet, and manually sort it. Run a binary diff tool on the result of the dump from SSIS and your hand-sorted example. If everything checks out, it's right.

OTOH, unit testing the Sort in SSIS shouldn't be necessary, unless what you're really testing is the sort criteria selection. The sort should have been tested by MS before it was shipped.

Answer 3

I would automate the testing by having a known good file for appropriate inputs which is compared binarily with an external program.

Answer 4

I like to use data viewers when I need to see the data moving from component to component.

Answer 5

SSISTester can tap data flow between two components and save the data into a file. Output can be accessed in a unit test. For more information look at ssistester.bytesoftwo.com.An example how to use SSISTester to achieve this is given bellow:

[UnitTest("DEMO", "CopyCustomers.dtsx", DisableLogging=true)]
[DataTap(@"\[CopyCustomers]\[DFT Convert customer names]\[RCNT Count  customers]", @"\[CopyCustomers]\[DFT Convert customer names]\[DER Convert names to upper string]")]
[DataTap(@"\[CopyCustomers]\[DFT Convert customer names]\[DER Convert names to upper string]", @"\[CopyCustomers]\[DFT Convert customer names]\[FFD Customers converted]")]
public class CopyCustomersFileAll : BaseUnitTest
{
    ...
    protected override void Verify(VerificationContext context)
    {
        ReadOnlyCollection<DataTap> dataTaps = context.DataTaps;

        DataTap dataTap = dataTaps[0];
        foreach (DataTapSnapshot snapshot in dataTap.Snapshots)
        {
            string data = snapshot.LoadData();
        }

        DataTap dataTap1 = dataTaps[1];
        foreach (DataTapSnapshot snapshot in dataTap1.Snapshots)
        {
            string data = snapshot.LoadData();
        }
    }
}