Learn how to a build a cloud-first strategyRegister Now


Scheduled DTS pkg fails but runs manually

Posted on 2006-05-22
Medium Priority
Last Modified: 2013-11-30
I ran a DTS package that I created (sync's 2 DBs on 2 servers) manually and it runs successfully.  But as soon as I run it as a scheduled task, it fails.

What causes this?
Question by:ttelesca
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16733817
It will be asssosciated with SQL server agent Account / with permissions.
Make sure that the SQL server has started
If it has already started then, the account with which it has started has enough permission to execute the Package

read this article

LVL 23

Expert Comment

ID: 16733825
Probably because the sql agent account does not have some sufficient level of privileges to run the job from A to Z...Hope this helps...

Author Comment

ID: 16733986
aneeshattingal, I'm checking out that article now.

Racimo, where would I find the SQL Agent account priviledges?  Can I change what the privil. are set to?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 12

Accepted Solution

ill earned 2000 total points
ID: 16733990
re-save DTS under different name. while saving, set it to run under your account.
LVL 23

Expert Comment

ID: 16734050
The SQL Server Agent account is usually the local service account which can do pretty much anything except:

> when it involves doing SQL tasks on another server
> when it involves doing LAN level tasks in general

What do you job do anyway?

Author Comment

ID: 16734139
We have 2 severs (across a VPN).  One server stores 2 main DBs.  The 2nd server is a backup server.  This is where the job is saved and running.  The job itself is set to copy both DBs and transfer them to the 2nd (backup) server.

When I run the pkg manually, its successful.  I'm actually just reschedule the job and I'm running it as we speak.  I'll see if this helps.
LVL 23

Expert Comment

ID: 16734280
That's probably because your local machine account on the Production Server does not have privileges to copy and write down data to the Failover Server...My advice to you is to create a service account for SQL Server then assign rights to that service account to perform all required actions on the Failover Server...

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question