Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Data Transformation Services Revisited

    I am trying to export a table from SQL Server to Oracle8i home edition. I want to schedule the export package to run at a certain time. The package gets created, but when the time is reached for the package to execute, it doesn't execute. There are no errors at all. It just doesn't run. The package is there because I see it in the Data Transformation Services->Local Packages list. Any ideas? Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    If you scheduled the package to run you should see it Enterprise Manager under the server in Management/SQL Server Agent/Jobs.
    There you will see last run status among other things.

    Of course the SQL Server Agent needs to be running.

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
    Thanks

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by exdter
    Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
    Thanks
    Do a refresh on the jobs. If it isn't there, then that is your problem. After you right-click the DTS package and do schedule, you should see the job in the "Jobs" panel.

  5. #5
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    I don't think you would get an error that it is not running

    In Enterprise Manage you see if it is running by the small green "play" icon on it. You can start it from there with right clicking on it.
    Or you can start it with Service Manager.

    It would probably be a good thing to set it to always start automatically.

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    The jobs were there, all with error messages. The Server Agent is running as well.

  7. #7
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    Then you know the schedule works at least :-)

    Can you run the task manually, and does it work without errors then?

    Originally posted by exdter
    The jobs were there, all with error messages. The Server Agent is running as well.

  8. #8
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by exdter
    The jobs were there, all with error messages. The Server Agent is running as well.
    OK, that's a different error than what you were seeing before (no jobs).

    Try double-clicking the job, click the "steps" tab, select the only step, click edit, cut and paste the command to a command prompt and see what you get.

    That will probably fail and give you a more detailed error. Otherwise, you probably have a permissions issue.

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    Manually the job works. Thanks for your help.

  10. #10
    Join Date
    Aug 2003
    Posts
    328
    When I put the line in a command prompt, everything worked. The table was created in Oracle.

  11. #11
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by exdter
    When I put the line in a command prompt, everything worked. The table was created in Oracle.
    If the job reliably fails when automated and reliably works when executed manually, it is almost definitely a permissions issue. Right-click the job, do "Start Job", wait a few minutes, do a refresh, and check if the job does reliably fail when scheduled.

    Check who owns that job. Try changing that to sa or local Admin. You also may want to try deleting and recreating the job (I've seen that help).

  12. #12
    Join Date
    Aug 2003
    Posts
    328
    I created the job logged in as administrator. I deleted and recreated the job more than once, and it still doesn't run on a schedule. Thanks for your help.

  13. #13
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45
    The scheduled job will run under the credentials the SQL Server Agent has if using Windows integrated security.
    For the connection with Oracle I guess you set that in the Oracle connection in the DTS package. (I don't know much about Oracle security).

  14. #14
    Join Date
    Aug 2003
    Posts
    328
    I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.

  15. #15
    Join Date
    Oct 2003
    Posts
    268
    Originally posted by exdter
    I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.
    This is an almost sure sign of a permissions issue. Did you look at the Windows Event Log? The error message for scheduled jobs should be in there.

Posting Permissions

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