I'm having problems with a DTS package that copies data from an Oracle database to a SQL database. The package runs successfully if it is manually executed however fails every time it is scheduled. The error given in the Job History is as follows:
Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0)
When I try to recreate the package on a test server or try to edit the package on the live server, I get two errors which are:
The Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.
You will be unable to use this driver until these components have been installed.
HResult of 0x80004005 9-2147467259) returned
Unexpected error occurred. An error result was returned without an error message.
This was using Microsoft ODBC Driver for Oracle and I've tried recreating the package with the OLE DB Provider for Oracle and get the Hresult error but no Oracle driver error. I've also tried updating the Oracle driver on the server to no avail!
Does anyone have any ideas what the problem could be?
You need to install the Oracle client on the test server, I assume you know how to do this through the Oracle OUI software. As for the first problem, when you execute the package via DTS designer, it uses your local settings, but via the agent I'm pretty certain it uses the account that starts the agent service and it's permissions on the respective servers.
Have you tried executing it via DTSRun on both your workstation and the server ?