Re: Resuming a failed DTS package - Proper ETL design with DTS pac
- From: "softengine" <softengine@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 11:37:35 -0700
unfortunately, we won't be moving to 2005 until 2090.
On the second solution, if I were to copy all the tasks on and after the
failure to another package, what would be the best way to roll back or
restore the table that the task failed on.
I can't rerun the new temporary packatge unless the the table that the
failed task was updating is restored.
"Allan Mitchell" wrote:
> In the next release of DTS (SQL Server Integration Services) this kind of thing is a breeze. In DTS however it is not. The way I
> would approach it would be to modularise the loads so that you then have checkpoints at which you can return after fixing any
> errors.
>
> I have seen solutions where everything after and including the task that failed was copied into another package so that this package
> could then be run after fixing any errors.
>
>
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - SQL Server 2005 Integration Services.
> www.Konesans.com
>
>
> "softengine" <softengine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F5F4DDE9-2607-47B4-BF86-8B6C1A9A156C@xxxxxxxxxxxxxxxx
> > 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
> >
> >
> >
>
>
>
.
- References:
- Resuming a failed DTS package - Proper ETL design with DTS package
- From: softengine
- Re: Resuming a failed DTS package - Proper ETL design with DTS package
- From: Allan Mitchell
- Resuming a failed DTS package - Proper ETL design with DTS package
- Prev by Date: Scheduled job fails but runs when.....
- Next by Date: Re: Scheduled job fails but runs when.....
- Previous by thread: Re: Resuming a failed DTS package - Proper ETL design with DTS package
- Next by thread: RE: Resuming a failed DTS package - Proper ETL design with DTS package
- Index(es):
Relevant Pages
|
