The developers here wrote a DTS package that transfers 7 tables from DB2 to SQL. Every once in a while one of the tables on the destination, SQL, will be empty after the package completes. It does not cause the job to fail with a error. They asked me to take a look...

The package is typical. Two connections with 7 transformations between them and a SQL task that truncates the destination tables with an On Completion work flow pointing to the source server.

I took a look at the Workflow properies for the transformations and noticed that only one of them had the SQL task as a precedence. I modified all the transformation to have the SQL Task that truncates the tables in the Precedences list under Workflow properties. The package is running OK for now.

My question is...

Since the Transformations did not have a precedence task, could the data have been copied and then the truncation occured if the timing was right?

Also how can a transformation be added and the precedence not set if it exists. I am going to test this to see if you have to explicitly set this depending on the order of the addition of transformations and On Completion work flows.

This may be a warning or best practice to check the Workflow properites to make sure the Precedences are set the way that you expect.

Thanks,
Norman

In message <CF9A9AE6-0ED7-4B73-BE5A-2E459561FB4E (AT) microsoft (DOT) com>, Norman
<anonymous (AT) discussions (DOT) microsoft.com> writes
I took a look at the Workflow properies for the transformations and
noticed that only one of them had the SQL task as a precedence. I
modified all the transformation to have the SQL Task that truncates the
tables in the Precedences list under Workflow properties. The package is
running OK for now.

My question is...

Since the Transformations did not have a precedence task, could the data
have been copied and then the truncation occured if the timing was
right?

Yep. Guess it would be pot luck which executed first, and tasks can also
execute in parallel.

Also how can a transformation be added and the precedence not set if it
exists. I am going to test this to see if you have to explicitly set
this depending on the order of the addition of transformations and On
Completion work flows.

It would be simpler to just delete the precedence constraint. Whilst it
could be fudge, there us no reasonable way to do this in the UI.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org