Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Oliver wang
Yak Posting Veteran
50 Posts |
 Posted - 2011-11-08 : 08:51:01
|
| hello everyone,I created a dataflowtask that will transfer data from excel file to a local database. The problem is when there is an null value which prevent the package from running and I stop the package, some of the values has already been transferred into the database which is not what I want(I want the table empty if an error happens). Could anybody shed light on where i can do configuration to rollback the package if some error happen. Please tell the detailed steps to avoid any misunderstanding. Thank you very much for your effort.regards,Oliver |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 09:04:19
|
| you need to enable transaction inside SSIS. you can do this by selecting the package or data flow task (depending on how you want scope of transactin to be), then click on properties icon to show the property and set transaction property to be required. then any failure will cause rollback of whatever processing it has done since start of transaction.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 09:05:48
|
| Also one thing note here is that you need to enable the MSDTC service in services.msc if you want to use transaction in ssis else it will fail with error distributed transaction coordinator not running------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Oliver wang
Yak Posting Veteran
50 Posts |
Posted - 2011-11-08 : 10:08:43
|
| Wow~buddy, are you a prophet?? Actually what really bothered me was related to your second advice~ Thanks a million for your effort~ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-08 : 10:12:03
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
How to configure rollback in SSIS