Unit testing data flow in a ssis pakage

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

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 2

Thanks, my exemple is not necessary the best. I don't want to test the framework, like you said i want to ensure that the sort criteria selection is the good one.

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

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 5

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