SSIS rollback

Posted on 2009-12-25
Last Modified: 2013-11-10

I have a SSIS that insert a lot of comments that we received, becasue our pdn server is offsite, when I am loading the comments to pdn server, my concern is if the internet dropped, I need to roll back the transaction, is there a way to do this? or if there is a better way?  thx
Question by:mcrmg
    LVL 30

    Expert Comment

    by:Reza Rad
    LVL 31

    Expert Comment

    by:James Murrell
    along with above you need to undertsand
    LVL 5

    Accepted Solution

    YOu can put the steps in the package inside of a transaction to rollback the entire transaction if any problem occured is a video on how to do it....

    To watch the video just need to register on the site and after confirmation you can watch it.

    Note about Transaction:
    Transactions in SSIS allow you to protect your data and envelope any data change that occurs in the package in that protection. Transactions can be either on the entire package, a container, or an individual task. They can be nested just like in TSQL. What is important to note about transactions is that only data-related tasks are protected. If your package were to archive a file, for example, that file would not be un-archived upon a package failure (unless you explicitly created some kind of compensating action to perform such duties).

    Lets take the previous package you created and wrap the package in a transaction. First, lets disable the checkpoint from the previous example by changing the CheckpointUsage package property to Never and the SaveCheckpoints property to False. Next, delete all the records from the RestartabilityExample table.

    With the example now reset back to its original state, youre ready to enable transactions on the package. In the Properties window for the package, change the TransactionOption at the bottom of the window to Required. By changing this from Supported to Required, you have created a transaction that envelopes the package. Any task or container that has this same property set to Supported will join the transaction. By default, each task is set to join the transaction with the TransactionOption of Supported set.

    For you to enable this type of transaction, though, you need the Microsoft Distributed Transaction Coordinator (MSDTC) started on each server that you want to participate in the transaction. So, if you have a server that changes data on two servers and runs on a third server, you would need MSDTC running on all three servers. Transactions can then protect data nearly from any database like SQL Server, Oracle, and UDB, since you have externalized the transaction-handling to another service. The caveat with this, though, is the database must be running on a Windows machine, since MSDTC is a Microsoft service.

    Before executing a package that has transactions enabled, you must ensure that MSDTC is started. You can start the MSDTC service in the Services applet, or you can type the following command from a command prompt: NET START MSDTC. If you did not have the service started, you would receive the following error when executing the package:

    Error: The SSIS Runtime has failed to start the distributed transaction due to
    error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction
    failed to start. This could occur because the MSDTC Service is not running.
    After having the service started, lets break the package yet again. Select the last task, which is named Insert Record 2, and change the ForceExecutionResult package property to Failure. Execute the package again and you should see the results as shown in Figure 7-11. This time, if you were to view the records in the AdventureWorks database, you should see no new rows. This is because the purging of the table occurred, then the first record was inserted, and when the final task failed, both it and the first two operations (within the same transaction) were rolled back.


    Author Comment

    I did put the stepes in the seq container, and it works great, lets say there are 2 steps in the container, if first step fails, it will roll back, but what if in my case, the internet drops?? does PDN server rolls back to previous state?  thx
    LVL 5

    Expert Comment

    If your internet is droped then connection will be cut on the time so full the transaction will be rollback to previous state. It is nothing like that wait in middle stage and again reconnect when internet is on.

    After esatablised the connection again it start from frist step.

    Author Comment

    great, so just to confirm, in my case, it should be fine to put two steps in a container?  thx
    LVL 5

    Expert Comment

    yes . You can put the two steps in a container.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now