December 16, 2003 at 7:13 am
I am having trouble getting several DTS packages to run as a job overnight. The DTS package works fine when I run it locally and on the server but when I schedule it as a job it fails. Do I have to setup a special user or something? I am the SQL and Machine Admin and have set ownership up as me.
December 16, 2003 at 7:36 am
What service account do you use to run SQL Server Agent service? It has to have sufficient priviliege as yours in order to run your job successfully. Or setup SQL Agent Proxy Account as an alternative.
December 17, 2003 at 2:31 am
I had a similar problem a while back. The SQLServerAgent had domain administration rights. But certain jobs would seem to lock up. Investigations revealed that SQLServerAgent likes to run everything silently. As this wasn't possible, I used scheduled tasks (AT command) with the /interactive switch to run the DTS package an alternative.
December 17, 2003 at 5:52 am
If you are using multiple processors and thirdparty drivers, you might have to check the execute on main thread checkbox on workflow properties box in your package.
December 17, 2003 at 6:56 am
I've found it easier and safer to use DTSRUN and generate a Windows BAT file to execute the DTS package, then schedule that via W2K Scheduled Tasks.
December 17, 2003 at 1:43 pm
Hi, I had the same problem with running DTS packages. From the design window they
ran fine but in the job scheduler they did not run properly, ie : SQLAgent reported
them as run but in fact the logs showed that they had not, no errors were reported.
Interstingly enough they all ran as an NT Administrator (Win 2000 Server platform).
I could run them manually ie. From the command prompt DTSRUN ... and they would run
to completion. But SLQServerAgent well.. nope
So, I did some more research, it seems that DTS requires a new login when run from
the agent, the login must have SYSADMIN privs, which the NT Administrator has.
The long and short of it was that...
1: I created a new NT username with SYSADMIN privs.
2: Shutdown SQLServerAgent and started it again using my new NT Username as the
login username
3: Set all the DTS package jobs in scheduler to be owned by the new username
4: Run the job(s)
This worked for me just fine. This is a datawarehouse that is dedicated to one
area of business, hence your DBA may not be too happy with changing SQLServeragent logins
But at least you can see which tack I followed to get this running satisfactorily.
Seasons greetings to everyone.
Paul Scotchford
Brisbane, Qld
Australia
Paul Scotchford
Brisbane
Paul Scotchford
Brisbane
December 17, 2003 at 2:02 pm
Thank you all for your ideas! We'll see how it goes...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply