Transaction across multiple SqlExecute tasks?

Jun 18, 2011 at 1:43 PM
Edited Jun 18, 2011 at 1:57 PM


I have sql scripts residing in folders called Tables, Views, StoredProcs etc., and I want to automate their deployment. But I want to be able to rollback if anything fails. Is it possible to have a transaction block across multiple sqlexecute tasks?

I took a look at the Execute() function within SqlExecute, and it looks like you are opening a connection at the beginning, and closing it at the end... SO even if I do :

<SqlExecute Action="ExecuteScalar" Sql="BEGIN TRAN" .... >

<SqlExecute Action="Execute" Files="@(Tables)".... >

<SqlExecute Action="Execute" Files="@(Views)".... >

<SqlExecute Action="ExecuteScalar" Sql="COMMIT" .... >

it is not going to work. Is there any other way?

Edit : The order in which the folders are picked up is important, tables have to be created before triggers, and so on.

Much appreciated!


Jun 24, 2011 at 8:36 PM

We don't have support for 'long running' transactions. It's an interesting idea. I'll add it to the backlog.

As always, happy to accept contributions.


Jun 24, 2011 at 8:37 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.