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

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 (AT) discussions (DOT) microsoft.com> wrote

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:

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:

Certainly you can enlist most things in a TXN but not things like Exports to text file etc.


1. Enable transactions at the package level (Package Properties)
2. On Success between each task
3. In the workflow properties for each task (check "Join transaction if
present","Rollback Transaction on Failure")
4. Make sure MSDTC is started

NOTE: Not everything supports joining a transaction
--

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 (AT) discussions (DOT) microsoft.com> wrote

Allan, thanks for the steps. Just one more question. What is the performance
hit from this? On RAM? on size of the log file?

Our packages are big but I'm sure we could alleviate that problem by
modularizing.
--
bill


"Allan Mitchell" wrote:

Remember that DTS is no more than a client so I would expect the hit to be the same as had you used another tool.

--

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 (AT) discussions (DOT) microsoft.com> wrote