Post reply

Rollback possible for all packages that came before the failure?

  • September 25, 2006 at 6:26 am

    #113586

    Howdy

     

    The need is to rollback all the transactions that have occurred for a particular database if any of the tables fail.

     

    The current structure is this:

    A list of servers/dbs scheduled to be imported is returned.

    For each of these records a batch is started, the ID returned and time logged.

    A list of the tables to be updated for the selected server/db is returned.

    For each of these an Execute Package task calls the specific package and executes it.

    The batch is closed and the time logged.

     

    So as you can see I could make it to table 150 out of 200 and then fail. I would need to roll all of the previous 149 back.

     

    I am not sure that this can be done with the current way the SSIS is designed to call other packages singly.

     

    Any useful insight would be appreciated.

     

    Thanks

  • September 25, 2006 at 11:21 am

    #662319

    Yep.

     

    http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

     

     

  • September 27, 2006 at 10:47 am

    #662790

    Ok - I tried it out (copying the flow from what the wizard does), but now I am getting this error:

    ...failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    The above error is a lie. The connections are correct, the SQL parses, there are no parameters and the result set is set to None.

    The visual is this:

    any ideas would be appreceated.

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply