Automated DTS package will not run

I suspect this is a user-rights/security issue:
I have a DTS package that needs to run weekly. This DTS package runs fine
when you open it and select "execute". I have done the following to set
this up as an automated task:
1)Go to DTS/Local Packages and right click the package.
2)Select "Schedule" from the pop-up menu.
3)Change settings to "weekly" and set the time to the current time + 5
minutes
4)Go to Management/SQL Server Agent/Jobs and refresh
5)Find the job that you just created, right-click and choose 'properties'
6)Confirm the settings (Owner: <my login name chosen from list> ) and let it
run.
I set up the "Notifications" tab to send me an email on completion which it
does:
========================================
================
JOB RUN: 'PhoneImportFromHR' was run on 2/12/2004 at 2:30:00 PM
DURATION: 0 hours, 0 minutes, 11 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 32
(PhoneImportFromHR). The last step to run was step 1 (PhoneImportFromHR).
========================================
================
Due to the fact that the DTS package runs fine when I run it, but won't run
automatically, I am thinking that this is a security issue (ie. The
automated process does not think I have rights to run this package, but
recognizes me when I try to run it manually). I have tried other login IDs
in the "Owner" box (see step #6 above), but I always get the same result.
Can anyone tell me what I can do to get DTS jobs to run automatically?
Schoo
PS: Windows 2000 Server, running SQL 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided. since you got the email notification,
the job runs and you the notification is working fine. For the job to run
automatically, could you check the job schedule. You could right-click the
job, then choose 'properties'; In the 'Schedule' tab, you will notice the
schedule of how you job runs. If you want to change the schedule, you could
press the 'Edit' button. You could arrange a new schedule of the job, Then
the job will run automatically. You could confirm by right-click the job
and choose the 'View job history' and check the record of how the job runs.
For the 'Notification' button, you could check the 'Email operator', choose
one operator and select when to send the email notification.
Hope this helps. If you still have questions, please feel free to post your
message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for your response... perhaps I was not clear enough on what is
happening. All of the items you mention in regards to changing a schedule,
I am aware of. I am saying that the actual job starts, fails and I get an
email notifying me that the job fails (see original email in this string).
The issue is not how to run the job automatically or how to get it to email
me. The issue is that the DTS package will run fine if I open it up and
execute it, but not if I create a job and try to run it automatically. My
questions is: how can I get the DTS package (that runs manually without
errors) to run without errors automatically.
I hope this is more clear and also that you might shine some light on this
issue for us.
Scott
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
> Hi Scott,
> Thank you for using the newsgroup and it is my pleasure to help you with
> your issue.
> From the information you provided. since you got the email notification,
> the job runs and you the notification is working fine. For the job to run
> automatically, could you check the job schedule. You could right-click the
> job, then choose 'properties'; In the 'Schedule' tab, you will notice the
> schedule of how you job runs. If you want to change the schedule, you
could
> press the 'Edit' button. You could arrange a new schedule of the job, Then
> the job will run automatically. You could confirm by right-click the job
> and choose the 'View job history' and check the record of how the job
runs.
> For the 'Notification' button, you could check the 'Email operator',
choose
> one operator and select when to send the email notification.
> Hope this helps. If you still have questions, please feel free to post
your
> message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||questions are
1) who is the owner of the job
if owner of job is member of sysadmins fixed serverrole then job will run
dts in job owner's context
if owner of the job is not member of sysadmins then job will attempt to run
in sqlproxyaccount's context(if sql proxy account is configured) if not you
will get a failure
Olu Adedeji
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OEcdtEm8DHA.4044@.tk2msftngp13.phx.gbl...
> Thank you for your response... perhaps I was not clear enough on what is
> happening. All of the items you mention in regards to changing a
schedule,
> I am aware of. I am saying that the actual job starts, fails and I get an
> email notifying me that the job fails (see original email in this string).
> The issue is not how to run the job automatically or how to get it to
email
> me. The issue is that the DTS package will run fine if I open it up and
> execute it, but not if I create a job and try to run it automatically. My
> questions is: how can I get the DTS package (that runs manually without
> errors) to run without errors automatically.
> I hope this is more clear and also that you might shine some light on this
> issue for us.
> Scott
> "Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
> news:S0TW5ug8DHA.1992@.cpmsftngxa07.phx.gbl...
run
the
the
> could
Then
> runs.
> choose
> your
rights.
>|||Hi Scott,
Thanks for your update and Olu's reply.
Besides Olu's question, could you run the DTS package in the design windows
of the package? Is it successful or any information? When the mail is send
to you, what is the job history? You could also run the following code in
you Query Analyzer and collect the information of this job.
select * from msdb..sysjobs
select * from msdb..sysjobsteps
select * from msdb..sysjobhistory
select * from msdb..sysnotifications
select * from msdb..sysjobservers
select * from msdb..sysjobschedules
I am waiting on your reply. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott:
How about everything going? Now I would add some more information on this
issue:
There is one good article for your reference:
269074 INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
Also, you could add the error file to capture the detailed error happened
when run the DTS package:
In the DTS package design window, choose from menu, 'Package'->'Properties'
and set the options in the 'Logging Tab'. You could refer to the DTS
Package Properties (Logging Tab) in the SQL Server Books Online.
Hope this helps. If you still have questions, please feel free to post
message here and I am ready to help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.