Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Unanswered: DTS Scheduled job always fails

    Hi,

    I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

    However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

    Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!

  2. #2
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9

    Question Re: DTS Scheduled job always fails

    Did you ever find a solution to this problem? I am having the same problem.

    Originally posted by multiplex77
    Hi,

    I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

    However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

    Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?

  4. #4
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    What message is returned for the failure ?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?

  7. #7
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions

    Originally posted by rnealejr
    ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Who is the owner of the job ?

  9. #9
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.

    Originally posted by rnealejr
    Who is the owner of the job ?

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    log the package and post the message(s) returned in the log.

  11. #11
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    I don't see a way to log the package. I've never had to do that.
    Originally posted by rnealejr
    log the package and post the message(s) returned in the log.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.

  13. #13
    Join Date
    Dec 2003
    Location
    Ohio
    Posts
    9
    The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?

  15. #15
    Join Date
    Feb 2002
    Posts
    2,232
    Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •