Very Computer

However, if I use an insert statement in a Execute SQL Task to accomplish
the same task,  the package executes successfully when run manually and when
run from a job.

Can anyone explain why jobs running with Transform Data Task sometimes fail
as shown below?

 DTSRun:  Executing...   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; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
step failed.

It could be a logonproblem. In the DTS package you have stated username in
the connections, but when you run it as a job it is the SQLSERVERAGENT that
is executing the package, and if the user that runs the AGENT service hasn't
rights on the SQL-Server you are connecting to, there is a problem in the
validation.

Regards

Kim Hansen

Quote:> I have experienced the following issue in several DTS packages.  I use
> Transform Data Task to transfer data from a table on one SQL Server to a
> table on another SQL Server.  The package executes successfully when run
> manually and fails with the error shown below when run from a job.

> However, if I use an insert statement in a Execute SQL Task to accomplish
> the same task,  the package executes successfully when run manually and
when
> run from a job.

> Can anyone explain why jobs running with Transform Data Task sometimes
fail
> as shown below?

>  DTSRun:  Executing...   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; 3000 Rows have been
> transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
> OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
> copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
> DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
> PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
> DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
> PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
> step failed.

Thank you for your reply.  I understand about the issues with security.  What is perplexing about my problem is that the Transform Data Task will not execute from a job, but the Execute SQL task (accomplishing the same thing) does execute successfully from the same package.  What I do not understand is, why an Execute SQL task works when the Transform Data Task does not.  The problem appears to lie with the Transform Data Task.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!