Post reply

SSIS Transaction to roll back file creation

  • June 25, 2009 at 10:18 am

    #133446

    Hello,

    I have an integration services package which updates a number of records (using execute SQL tasks) then exports several files to various different locations (using data flow tasks). Thus:

    Update 1 -> Update 2 -> Data flow 1 -> Data flow 2 (where all the arrows indicate on success).

    I have set it up with transactions for the package (required), and each of the above items is set to have transactions supported.

    I would like it to roll back the creation of the file made in Data flow 1 if Data flow 2 fails - the rollback of the updates is working fine.

    Is there any way that I can make the file creation be included in the transaction?

    Hope this makes sense - I'm new to the world of SSIS.

    Thanks,

    Rachel.

  • June 25, 2009 at 11:02 am

    #1015471

    You can't actually roll back as such in SSIS (as far as I know), to achieve what you want you would need a "File System Task" to run if your job fails (or what ever criteria you might want using SSIS Variable) on the Second data task to delete the file created by the first data task.

    Hope I have made sense! If not I can knock a quick example up and send you.

  • June 26, 2009 at 2:00 am

    #1015826

    That makes perfect sense.

    Thanks.

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

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