Hi
How can commit interval for OLE DB destination be set when the data access mode is not "fast load".
What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.
Thanks,
Vipul
Vipul123 wrote:
Hi
How can commit interval for OLE DB destination be set when the data access mode is not "fast load".
What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.
Thanks,
Vipul
"Commit interval" when you're not using fastload is 1. Each row is an independent insert. If there is a failure in the destination, the row can be redirected, you can ignore it, or the component can fail as determined by the error disposition of the component. If you're using transactions, then the rollback is managed by the server using the transaction log, not the oledb destination. Committing a bulk load batch and committing a transaction are not the same thing.
|||
Hi
How can commit interval for OLE DB destination be set when the data access mode is not "fast load".
It can't. Use fast load. And why are you against using fast load?
What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.
Thanks,
Vipul
When you are not using fast load, your roll back option is limited to ONE row unless you've enrolled the entire data flow in its own transaction (BEGIN TRANSACTION) or are using DTC. When you are not using fast load, if one row fails, that one row gets rolled back, while the others are left untouched -- including future rows depending on how many errors you've configured the package to accept.
No comments:
Post a Comment