Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 25th, 2004, 05:00 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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

  #2 (permalink)  
Old May 25th, 2004, 05:26 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
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
  #3 (permalink)  
Old May 25th, 2004, 05:43 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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
  #4 (permalink)  
Old May 26th, 2004, 09:08 AM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
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
  #5 (permalink)  
Old May 26th, 2004, 11:27 AM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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
  #6 (permalink)  
Old May 26th, 2004, 11:38 AM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
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
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 01:47 PM
DTS Scheduled Jobs Errors Walden SQL Server DTS 10 March 16th, 2004 09:32 AM
Execute DTS on Scheduled Error Jane SQL Server DTS 5 November 21st, 2003 04:20 AM
Got duplicated records after running scheduled DTS hock_heng_ooi SQL Server DTS 0 August 27th, 2003 08:56 PM



All times are GMT -4. The time now is 05:09 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.