SSIS unit test targets a package or a particular task. Where unit test targets a package or particular task, integration test targets the whole ETL process, but with the same precision as unit test. Integration tests are implemented as post conditions that need to be satisfied after the package or task execution.
SSIS Tester can tap paths between data flow components. This enables you to unit test different paths in a data flow and make sure that expected data comes out at all points. This feature is available for SSIS 2008, 2012 and 2014. Fake source and destination components allow you to eliminate external dependencies. Data captured by the data tap or fake destination can be easily accessed in a test.
Whether you need unit or integration test, you are not required to implement more than 3 methods. Unit tests have 3 simple steps and follow the common methodology by performing test setup, result verification and cleanup. SSIS Tester API provides an easy and intuitive way to access package variables, properties, connections… during these phases. You can execute SQL commands against DB easy.
SSIS Tester does not require a package to be prepared or adapted in any way in order to be tested. It can be tested right away just as it is. This is great advantage since you do not have to implement test logic in your packages. Not to mention the situation when you can or may not change existing packages.
SSIS Tester comes with a user interface which enables you to monitor test executions in real time. Test parameters such as currently executed test, test errors, or assertion results are available. Test results can be exported to HTML, so you can easily save or send them per email.
You can use any of .NET languages to write tests. Even not experienced SSIS developer can do it if you describe precondition and postcondition. Since you are required to write c# or vb code in script tasks it should not be difficult to use .net. Why use c# or vb? It is faster to write code, better in test-throw away phase, debug your test during execution….NET powerfult APIs enable you to write mockups, stubs for packages…
SSIS Tester comes with a ready to use project template. This template contains all you need to start implementing tests. Two empty skeleton classes with instructions are automatically created for the each test type. The template contains a demo with sample SSIS packages and implemented tests as well. Demo can be compiled and started right away. Just use Visual Studio debugger and step through the code.