February 11, 2005 at 1:09 pm
Hi, I need to get data from Oracle to SQL server. I am trying to schedule a DTS package and run it daily. But the SQL job is using the sql agent account to access Oracle. The sql account doesn't have access to Oracle. The job fails to run. It will run successfully if I mannually run the DTS package. Any help will be greatly appreciated!
Thanks,
Yuhong
February 14, 2005 at 7:17 am
What exactly is the error message and how are you trying to link to Oracle? Is the problem that the package won't run or that the package runs and errors.
One common problem is that you will access Oracle via a DSN (ODBC), when you run the package manually from a client machine the DTS package looks at the client setup and picks up the DSN from the client machine. When you run from a job this is done entirely on the server and the same DSN name is searched for on the server, this either does not exist or something else exists with the same name. In this case you need to ensure that identical ODBC connections are created on client and server so that the live run will act the same as your manual test.
Sometimes the problem can be the ownership of the package itself but this doesn't run the package at all.
Hope this helps
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply