DTS Problem - Copy SQL Server Objects Task

Question

DTS Problem - Copy SQL Server Objects Task

suresh_yaram

SSC Veteran

Points: 267
More actions
June 26, 2006 at 11:54 am

#68320

Hi All

WE have a DTS package containing multiple 'Execute Package Task' objevts executes various DTS packages serially. While exectuing the underlying package, SQL Sevrer creates varius files under ...\Program Files\Microsoft SQL Server\80\Tools folder with extensions PRC, ID1, ID2, DR1, DR2 etc....

Now the problem is, we get following error.

Step 'DTSStep_DTSTransferObjectsTask_10' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The system cannot find the file specified.
(Microsoft Data Transformation Services (DTS) Package (80070002): The system cannot find the file specified.
) (Microsoft SQL-DMO (80004005): [SQL-DMO]CreateFile error on '<Server>.<db>.DR2'. The system cannot find the file specified.

But, this task will be successfull if it is executed manually. Please let me know if you have any clue on this issue.

Thanks in advance,
Suresh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sree-226516 SSCrazy Points: 2590 More actions · Answer 1

Sorry ,I am not very clear with you question Suresh.You mean to say when you are executing as a sql agent job its failing where as manually from your local system its working.

Thanks,

SR

Thanks,
SR

suresh_yaram SSC Veteran Points: 267 More actions · Answer 2

Hi SR

WHen the entire package has been executed as whole, it fails with the error given. When the particular task is executed individually (manually), it is successfull.

Thanks,

Suresh

sree-226516 SSCrazy Points: 2590 More actions · Answer 3

Are you using UNC path while specifying the path for a file?

Thanks,

SR

Thanks,
SR

suresh_yaram SSC Veteran Points: 267 More actions · Answer 4

No, these packages run as local packages on sever.

Thanks,

Suresh

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 5

I've found that if you execute a package step by step, it will run using the local and mapped drives on your computer. If you execute the entire package, it will run using the local and mapped drives on the SQL server.

Maybe the problem is that the paths to files you're using include mapped drives that exist on your computer and not on the server.

Michael Tocik SSCertifiable Points: 7041 More actions · Answer 6

Hi

I would suggest that you get on the server (log on the server) then try each step and should each step run correctly then schedule your job and it should work

Mike