DTS Package Execution Order

I have a package that has 12 data pump tasks all executing in parallel.

It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.

Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.

This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.

Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.

I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.

Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.

To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.

For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.

dts package errors

I am transferring data from a text file to a table in SQL Server. Is
there any way to export the dts package logs or a similar error log to
a text file. I need to know if the errors are duplicate keys or
missing files. I don't know if I need to write some code or what I
need to do. Any help'It largely depends on what type of transformation you're using. If you are
using the Transform Data Task then on the options tab of the task properties
you can set a filename for the "Exception file". If you uncheck the '7.0
format' option and select the other 3 check boxes you'll get an exception
file and 2 other files - each containing the erroring rows. If you are using
the BULK INSERT task then there is no such granular option. You could try
package logging (right-click on the white background, "Package Properties",
"Logging" tab and "Error file:" text box) but AFAIR you don't get much
useful info there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have tried the exception file but if the file does not exist there is
no error written to that file. I will need to know this.
Paul Ibison wrote:
> It largely depends on what type of transformation you're using. If you are
> using the Transform Data Task then on the options tab of the task properties
> you can set a filename for the "Exception file". If you uncheck the '7.0
> format' option and select the other 3 check boxes you'll get an exception
> file and 2 other files - each containing the erroring rows. If you are using
> the BULK INSERT task then there is no such granular option. You could try
> package logging (right-click on the white background, "Package Properties",
> "Logging" tab and "Error file:" text box) but AFAIR you don't get much
> useful info there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

dts package errors

I am transferring data from a text file to a table in SQL Server. Is
there any way to export the dts package logs or a similar error log to
a text file. I need to know if the errors are duplicate keys or
missing files. I don't know if I need to write some code or what I
need to do. Any help'It largely depends on what type of transformation you're using. If you are
using the Transform Data Task then on the options tab of the task properties
you can set a filename for the "Exception file". If you uncheck the '7.0
format' option and select the other 3 check boxes you'll get an exception
file and 2 other files - each containing the erroring rows. If you are using
the BULK INSERT task then there is no such granular option. You could try
package logging (right-click on the white background, "Package Properties",
"Logging" tab and "Error file:" text box) but AFAIR you don't get much
useful info there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have tried the exception file but if the file does not exist there is
no error written to that file. I will need to know this.
Paul Ibison wrote:
> It largely depends on what type of transformation you're using. If you are
> using the Transform Data Task then on the options tab of the task properti
es
> you can set a filename for the "Exception file". If you uncheck the '7.0
> format' option and select the other 3 check boxes you'll get an exception
> file and 2 other files - each containing the erroring rows. If you are usi
ng
> the BULK INSERT task then there is no such granular option. You could try
> package logging (right-click on the white background, "Package Properties"
,
> "Logging" tab and "Error file:" text box) but AFAIR you don't get much
> useful info there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .