I am having a problem with a couple of DTS packages, which only recently
appeared.

The package is fairly simple, with the following steps:

Step 1: ActiveX Script Task which creates a folder in a certain
directory. This task uses a UNC path for the file, not a mapped drive.
Step 2: ActiveX Script Task which checks our FTP site for files.
Step 3: ActiveX Script Task which moves the file it found, decrypts it
(we use PGP encryption and use
command line switches to do this, then sets the
data source (this is a global variable used in the
Connection 1 Task)
Step 4: On Success of Step 3, a send mail task sends a notification
message to a user. On Failure of Step 3, a
failure notification is sent.
Step 5: Connection 1 is a flat file at the location provided in the
global variable for the data source. This data is
pumped into a SQL table, found in Connection 2.
Step 6: On Success of the data transfer, an Execute SQL task deletes
one row from the table.

When run through DTS Designer, all steps execute except the last one (Step
6). That one will have the message (NOT RUN), not that if failed. If I
then manually execute Step 6, runs fine.

When run as a job, it doesn't get past Step 3 where it moves the encrypted
file to the folder created in Step 1.

When I view Job History, this is the message I find:

Executed as user: NCN\svcSQLAdmin. ...
Start: DTSStep_DTSActiveScriptTask_2
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2
DTSRun OnStart: DTSStep_DTSActiveScriptTask_3
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_3
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421
(8004043B)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2147220421 (8004043B);
Provider Error: 0 (0)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSSendMailTask_2
DTSRun OnError: DTSStep_DTSSendMailTask_2, Error = -21472203...Process Exit
Code 2.
The step failed.

I have tried searching this error and have not yet found any meaningful
help.

I have similar packages that work flawlessly, including the decryption part
and execute SQL.

I'm hoping one of the experts on this forum can help me out with this. This
is seriously impacting performance, as this data is very important to us and
it is easy to forget to go and manually execute this DTS package, as well as
then go and manually execute Step 6.

Thanks in advance,

Deborah

Have you got more than one workflow constraint leading to the step that
does not execute? If so then both constraints must be satisfied, as DTS
uses AND logic here not OR.


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

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

No, only the one constraint.


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote