RE: Resuming a failed DTS package - Proper ETL design with DTS package
- From: "softengine" <softengine@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 16:46:03 -0700
I found the excerpt below from SQL Books online. Is this the right direction.
It sounds like they are saying that SP3 allows me to leverage MS DTC to
rollback transactions.
Somehow I'm supposed to be able to rollback and entire DTS package if it
fails all the way through?
Is this practical for large packages?
How would I do this?
You use database transactions to bind multiple updates into a single atomic
unit. In this way, you help to ensure that your data remains in a consistent
state. Distributed transactions carry this concept a step further, allowing
you to bind disparate operations on multiple platforms into a single
transaction.
Data Transformation Services (DTS) uses functions offered by the Microsoft®
Distributed Transaction Coordinator (MS DTC) to extend the benefits of
distributed transactions to the DTS package developer. For transactions to
work, MS DTC must be running on the computer executing the package. Use the
SQL Server Service Manager to start MS DTC or to verify that it is running.
With DTS transactions, you can:
Gather the results of several tasks into a single transaction and so ensure
consistent updates. For example, orders and line items can be uploaded by two
tasks, which succeed or fail together.
--
bill
"softengine" wrote:
> We have a few large DTS packages that make up our ETL process for our Star
> schema reporting database. These packages run every night.
>
> If one of those packages were to fail mid-way through, we would have to
> track down the error(which is whole 'nother problem) and then restore the
> entire (very large) database from the last days backup before we could
> restart the the failed package. The database has to be restored because of
> history in some of our tables and just the mere complexity of the Star
> Schema.
>
> Is there an article or a DTS design philosophy that allows me to just fix
> the problem and restart the failed package without having to restore the
> entire database?
>
> Here is an idea I have been throwing around
>
> For each DTS task, make a backup of the table being imported or refreshed
> before the task runs. When a step fails, log the failed step and after the
> problem has been fixed re-run the package, except this time it will know that
> it is recovering from a failure and refresh the table that was backed up and
> continue the rest of the DTS package.
>
> I'm not sure if the amount of work to do this is worth-it, but It would save
> us from having to restore a database that takes around an hour to restore and
> re-run a package in the middle of the night; and that's just once, what if
> the problem wasn't fixed?
>
>
> - Bill
>
>
>
.
- Follow-Ups:
- Re: Resuming a failed DTS package - Proper ETL design with DTS package
- From: Allan Mitchell
- Re: Resuming a failed DTS package - Proper ETL design with DTS package
- References:
- Resuming a failed DTS package - Proper ETL design with DTS package
- From: softengine
- Resuming a failed DTS package - Proper ETL design with DTS package
- Prev by Date: Re: keep FileSystemObject across phases
- Next by Date: Re: DTS in combination with access and as400(ibm)
- Previous by thread: Re: Resuming a failed DTS package - Proper ETL design with DTS pac
- Next by thread: Re: Resuming a failed DTS package - Proper ETL design with DTS package
- Index(es):
Relevant Pages
|
