Transaction support

Feb 17, 2009 at 4:28 PM
I was wondering if there is currently any intention of adding support for transactions at the Test, or TestSuite, level - mainly for the purpose of rolling back batabase changes after the test assertions finish.
Feb 17, 2009 at 9:18 PM
This poses some issues when you factor in that the package can have it's own transactions, outside of what the unit test framework controls. How would you see it working?
Feb 17, 2009 at 10:40 PM
Of course, rolling back a transaction is mostly a lazy mans test case clean up so that you only have to write the setup. That said, my thought was to open a transaction for the test case before executing the Setup, and then rolling it back after the TearDown, in the case of a task that is known to write to the database. Thus, thinking that any transactions in the task become nested within the test's transaction scope.

The idea was sparked in part when I noticed that the DtsContainer's Execute takes a transaction object, though I'm not sure in what context it uses it. I was mostly curious if it was possible to use in conjunction with testing package tasks.
Feb 18, 2009 at 4:16 PM
Edited Feb 18, 2009 at 4:17 PM
Anything that makes writing tests easier makes it more likely that people will actually write the tests, so I'm always in favor of things that improve the usability.

That being said, SSIS actually uses MS DTC transactions, rather than simple database transactions. That complicates things a good bit. I'm not sure if it's possible for me to wrap a transaction around that, but I'll look into it.

Also, I'm going to make this a work item, so I can keep track of it.
Feb 18, 2009 at 4:17 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.