MS SQL Server DTS - On success not working

Author

On success not working

Andre

2006-03-28, 8:27 pm

I have a DTS package that, in a simplified version, does this:

- step 1. script out indexes on tables to a text file
- step 2. truncates the tables
- step 3. drops the table's indexes
- step 4. transfers data, using the Transform Data Task. This step has one
"source" connection and one "destination" connection. I transfer 6 tables
from the source to the destination.

All steps are dependant on the previous step completing successfully.
However, what I see happening is this: step 1 finishes, step 2 starts, and 1
of the 6 tables in step 4 starts, then the next... I want all 6 tables to
wait for step 3 to finish before they start transferring, but they don't.
Why not? Is there an easy way to make this happen? I'm tempted to put all
my transform data tasks into another package and execute that package from
my main package. At least that way I'll know they are executing when they
should.

On a related but separate note. What is the fastest and best way to
transfer my tables; with one source/destination for all 6 tables, or with
six source/destinations - one for each table? And why?

Thanks, Andre

Satya SKJ

2006-03-29, 7:31 am

The steps you've defined should work and you can define the import process on
a seperate DTS package and then call this package from source package to
complete the process.

BTW what is the rowcount involved in the import and how many of them have
indexes?
--
-----------------
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.

"Andre" wrote:

> I have a DTS package that, in a simplified version, does this:
>
> - step 1. script out indexes on tables to a text file
> - step 2. truncates the tables
> - step 3. drops the table's indexes
> - step 4. transfers data, using the Transform Data Task. This step has one
> "source" connection and one "destination" connection. I transfer 6 tables
> from the source to the destination.
>
> All steps are dependant on the previous step completing successfully.
> However, what I see happening is this: step 1 finishes, step 2 starts, and 1
> of the 6 tables in step 4 starts, then the next... I want all 6 tables to
> wait for step 3 to finish before they start transferring, but they don't.
> Why not? Is there an easy way to make this happen? I'm tempted to put all
> my transform data tasks into another package and execute that package from
> my main package. At least that way I'll know they are executing when they
> should.
>
> On a related but separate note. What is the fastest and best way to
> transfer my tables; with one source/destination for all 6 tables, or with
> six source/destinations - one for each table? And why?
>
> Thanks, Andre
>
>
>

Andre

2006-03-29, 11:29 am

I have 5 tables that I drop the indexes/PK on. The number of rows are: 25
million, 1 mil, 4 mil, 2.5 mil and 2.5 mil. I have a few other tables that
I import that have less than 500k records, some as low as 60 records.

I'm interested to know your train of thought in this question. Do you have
some suggestions for the import?

Andre

Sponsored Links