Scheduled DTS package does not work
 

I have an ActiveX script that moves data from one table to another and writes a flat file from second table to a remote server. When I execute the DTS package manually it works fine. However, when I schedule this package to execute on a specific time of the day, the flat file creation part fails but the task runs successfully--data gets moved and appropriated flags put in place.

I wonder why the same package works flawlessly when executed manually but fails to work the same way when it is scheduled. Does anyone have a suggestion?

Nirav

Remember when you run manually - despite opening the package from another server even, the package is running under your security and is running off of your instance of SQL Server. Also; any drive referances should use UNC's not letters - as drive 'F' to you may not be drive 'F' to SQL agent.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928  fax (360) 902-6200

Joe,

Did you mean the remote server to be mapped correctly?

I have defined the flat file location as path = "\\172.00.0.001\dtsfile\"&fileName&".asp"

Does SQL Sever Agent need a write permission to the folder? If so, how would you recognize SQL Server Agent i.e what would be the user id etc.?

Appreciate your help.

Nirav

Yes, the user the agent runs under needs rights to write to the file system.

You can find the user yours is running under by checking in the services control pannel.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

I am new to the SQL Server. How do I get to the services control panel.

Thanks
Nirav

Go to contol pannels on the PC.. then select "services".... and that opens up the services control pannel... then you can check there...

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee