How do I perform automated unit testing in SSIS packages?

Question

How can I unit test SSIS packages? I want to be able to create and maintain unit tests for various components such as the workflow tasks, data flow tasks, event handlers, etc.

Are there any existing techniques, frameworks, and/or tools that can be used?

Answer 1

ssisUnit

A unit testing framework for SQL Server Integration Services

Answer 2

Nowadays, ssisUnit isn't up to date and exist modern unit testing framework for SQL Server Integration Services called SSISTester.

MSDN article

Nuget

Answer 3

some testing practices I usually follow when testing SSIS packages.

I always test at package level (it usually does not make a lot of sense to me to test at a lower level than this.... )

I usually keep a testing data environment with pretty small data sets.

Also a testing configuration profile (config files) pointing to the testing data sets and any other different testing parameters.

Depending of the nature of the project sometimes I also keep some database backups used to be restored whenever we want to reset the environment initial status (or any other statuses in the ETL process).

All of these combined in a good set of testing scripts (python, powershell...) calling the packages via dtexec, it's a pretty useful recipe for me ;-)