I have a DTS Package that drops local tables, recreates them and then
pulls new data from a database on a different server. At first I
couldn't get the package to run as a scheduled job at all I read KB
article 269074 and made sure the job would run on the server and that
SQL server agent runs as a domain account with sysadmin permissions
both locally and on the other server.
Now the DTS Package completes, but the Job says it failed. I verified
it the DTS package ran. (Event viewer also shows DTS package
successfully executing but the Job failing). When manually starting
the job from Enterprise Manager I get the same behavior. The job fails
even though the DTS package runs.
There is only one step in the job and on success is set to 'quite
with success' and on failure set to 'quit with failure'.
Here are the job history details (on example, they're all basically
the same):
Step ID 0 (Job Outcome), result - failed:
The job failed. The Job was invoked by Schedule 58
(DTS_ServerBToServerA 2). The last step to run was step 1
(DTS_ServerBToServerA 2).
Step ID 1 DTS_ServerBToServerA 2, result - failed:
Executed as user: DOMAIN\Administrator. ...:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 2180 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 2180 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_7 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_7 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_8 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_8 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_5 DTSRun OnFinish: DTSStep_D... Process
Exit Code 1. The step failed.

In the event viewer there are no warnings other than for
SQLSERVERAGENT, event ID 208, notification of failed job which
immediately follows the information event "The execution of the
following DTS Package succeeded"
Any ideas? I'd really appreciates some help...I'm pretty new to
SQL. One other thing I've noticed is that there's a newer service
pack for SQL. I'm using SQL Server 2000 8.00.760 Service Pack 3 on a
Microsoft Windows 2000 Server v. 5.0.2195 SP 4.

Hello mahalie (AT) gmail (DOT) com,

I know you have read the article about Jobs and DTS but one thing you did
not mention is the job owner? Anyways that said. It looks as though the
job gets so far and fails.

Error Message 208 in TSQL would be unknown object.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Ah yes, the job owner was changed to DOMAIN\Administrator as well.

I'm not sure what to look for next with an 'unknown object'. Where to
go from here? Does this indicate it is something in the SQL in the DTS
package? Perhaps the unknown object refers to this crazy command in
the Job (the only command, which initiates the DTS package):
DTSRun
/~Z0xD8C007929BB5B841E67EFB638FB851937369013FD7C90B F167BF7FB439F711D02FDECF8021842B4A5CCE3A21F68E3722 4BD063C9BAA12E385573517006E1A929765984A9D3D6DF0182 8C8EA63D5C788E5D9C72EF898523E097FB930B89ADA401C374 FF7704A792B2EA88D6


What I mean is, could it be referencings an expired command reference?
I'm not sure what that command line up there means, but it looks like a
GUID.

.....I'm at a loss!

~Mahalie

Allan Mitchell wrote:

Hello mahalie (AT) gmail (DOT) com,



This is the perishing encrypted command line you get by default when right
clicking a package and choosing "Schedule package". The fact is your package
executes so that is OK.

Try using package logging and not relying on the Job Step Logging




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks Allan...logging turned out to be the solution. Well, the KB
article mentioned earlier (269074) that I read and reread and thought I
understood turned out to detail my problem. Just when I thought I was
done with the package designer (I mean, the DTS package worked, so why
break it?) - I executed the package one step at a time to look for
anything suspicious. It all went off well, but at the end a log error
popped up. I took a look at Package Properties (accessible by right
clicking in the package designer - not on an object). On the logging
tab 'fail package on log error' was toggled. I left it toggled but
changed the path to the log file. It was pointing to something
invalid...I also changed the path from mapped drive to UNC. It finally
works both when I run it manually from Enterprise Manager on my
desktop, from the Server and as a scheduled job as the SQL Server
Agent's account! Whoopee!!

(Does this kind of stuff ever make you do an embarrasing dance in front
of your coworkers?)

~mahalie