SSIS Script Component Testing Strategy

Question

This question is in respect to the script component specifically. I am aware of ssisUnit etc…

With simple SSIS Scripts Components, it’s sufficient to let basic testing flesh out issues, however I am working with a script that has grown in complexity over time.

To better test the functionality I am considering abstracting the script logic into a DLL that gets deployed with the package, and then use the custom component in the script. The advantage is that the function will be more testable etc but it’s one more deployment artefact that needs to be managed.

My question is, does anyone know of a better way to test such an SSIS script in a more isolated manner than to run the whole package and examine the output?

Answer 1

CozyRoc has a Script Task Plus that basically allows you to create a repository of reusable scripts. This should allow you to manage and modify the scripts without having to adjust the packages directly. Also, this should allow you to ensure that common scripts have consistent (identical) code since you aren't copying blocks of text all over the place. I haven't used this feature before so I don't know if it will help, but it might at least eliminate the need of compiling DLL's. It won't help address the testability aspect, though, so the DLL solution may end up being better.