DTS Transform Data Task fails in job where DTS Execute SQL Task does not 
Author Message
 DTS Transform Data Task fails in job where DTS Execute SQL Task does not

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.



Mon, 16 Feb 2004 09:40:27 GMT
 DTS Transform Data Task fails in job where DTS Execute SQL Task does not

Hi

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.



Mon, 16 Feb 2004 18:42:50 GMT
 DTS Transform Data Task fails in job where DTS Execute SQL Task does not
Kim,

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!



Mon, 16 Feb 2004 21:14:57 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Execute SQL Task from within a Transform Data Task - hangs

2. DTS Task will not execute as SQL Server Agent job

3. DTS Execute SQL Task Output Messages to Jobs

4. DTS Custom task in C# and threading model of DTS C# custom tasks

5. DTS Task not executing in SQL Agent, works manually

6. Named Parameters not working in DTS Execute SQL Task

7. Gripe: DTS Designer Transform Data Task

8. DTS variable to a distination column in a data transform task

9. Bug in DTS Transform Data Task window

10. DTS Package and Transform Data Task Object

11. Passing parameters in Transform Data Task (DTS)

12. DTS Transform Data Task to Excel Problem


 
Powered by phpBB® Forum Software