I’d put in a request for additional support for unit testing in Integration Services on Connect (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276292). Unfortunately, it doesn’t look like it will make the cut for SQL Server 2008.
I used automated unit testing extensively when I was more focused on application development, and I really miss having it available in SSIS. In my experience, it made a significant difference in both quality and speed of development. Having a suite of test cases gave developers a safety net and being able to quickly test small units of work made progress much more tangible. (For more on test-driven development and some of the benefits, see http://en.wikipedia.org/wiki/Test-driven_development.)
Given that, maybe it would be worth consider incorporating some unit testing capabilities into the BIDSHelper project (http://www.codeplex.com/bidshelper) or starting another community project to add those capabilities. Any thoughts on what would be good features to include? My list includes:
- Ability to test tasks individually
- Ideally, ability to test pipeline components individually
- Standard xUnit functionality (the ability to set up and tear down a test)
- Some support for setting the database to a known state prior to the test would be valuable
Any other thoughts would be welcome.