Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 25th, 2004, 05:00 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old May 25th, 2004, 05:26 PM
Authorized User
 
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old May 25th, 2004, 05:43 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Quote:
quote:Originally posted by hortoristic
 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
 
Old May 26th, 2004, 09:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
 
Old May 26th, 2004, 11:27 AM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Thanks
Nirav

Quote:
quote:Originally posted by Hal Levy
 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
 
Old May 26th, 2004, 11:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
DTS scheduled jobs not finishing abob SQL Server DTS 0 July 13th, 2007 07:48 AM
DTS Package Fails When Scheduled kwilliams SQL Server DTS 4 December 23rd, 2005 02:47 PM
DTS Scheduled Jobs Errors Walden SQL Server DTS 10 March 16th, 2004 10:32 AM
Execute DTS on Scheduled Error Jane SQL Server DTS 5 November 21st, 2003 05:20 AM
Got duplicated records after running scheduled DTS hock_heng_ooi SQL Server DTS 0 August 27th, 2003 08:56 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.