Post reply

Roll Back All Transformations in DTS package

  • March 31, 2006 at 5:42 am

    #67355

    1) Transfer data from Excel 2003 file to SQL server.

    The data from the tables present in excel file have to be transformed to respective tables in SQL server. We have designed the transformations for all the tables available, in the DTS package. However, if any one of the transformations fails then all the other transformations must roll back along with the one that failed.

    How can we acheive this

  • March 31, 2006 at 8:11 am

    #630136

    Never really looked into it. I have found good articles on this sire before. Maybe it will help you out.

    http://www.databasejournal.com/features/mssql/article.php/3327681

    Come back to the forum with an answer if you get one or not as feedback is always appreciated.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • April 4, 2006 at 2:49 pm

    #630833

    1. Open Package Properties in Designer

    2. On the Advanced tab, check 'Use transactions' and 'Commit on successful

        package completion'

    3. Right-click on each each Data Transformation task and select 'Workflow

        Properties'

    4. On the Options tab, check 'Join transaction if present' and 'Fail package on

        step failure'

     

    This will ensure that all the transformations run in a single transaction which will be committed if the package succeeds and rolled back if the package fails.  The package fails if any step fails.

    Greg

     

     

    Greg

  • April 4, 2006 at 10:17 pm

    #630894

    hi,

    Thanks. It worked.

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

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