DTS Job

Question

Post reply

DTS Job

W R

Mr or Mrs. 500

Points: 523
More actions
June 8, 2007 at 9:01 am

#173982

I have a DTS package that I am trying to set up as a job. The DTS package executes when run adhoc but the DTS job fails.
DTSRun OnError: DTSStep_DTSDataPumpTask_1, 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) 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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Any help is greatly appreciated.
Thank you.
-W R

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736255

June 8, 2007 at 10:00 am

#711746

What does the job do? What does datapump task 1 do?

W R Mr or Mrs. 500 Points: 523 More actions · Answer 2

datapump task 1 does a select from another server into this server select col1, col2 from table

Thank you.

-W R

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

W R,

Look at the package log for better error descriptions. If you haven't enabled logging in the package, open the package in DTS Designer, select 'Package Properties', the Logging tab, and check 'Log package execution to SQL Server'. Click 'OK' and save the package.

Run the job again, right-click on the package and select 'Package Logs...'.

Greg

W R Mr or Mrs. 500 Points: 523 More actions · Answer 4

Thank you.

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers

Step Error Description:[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Step Error code: 80004005

Step Error Help File:

Step Error Help Context ID:0

-W R

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

A few questions:

"The DTS package executes when run adhoc but the DTS job fails."

When you run the DTS package adhoc (outside of a job), are you logged into the computer running SQL Server or running on different computer (your workstation) ?

"select from another server"

Is the other server Oracle ?

SQL = Scarcely Qualifies as a Language

W R Mr or Mrs. 500 Points: 523 More actions · Answer 6

Thanks for your reply.

I am logged on to the computer running SQL Server

The other server is Oracle. I use ODBC to get to the Oracle database.

-W R

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 7

I think that the 80004005 error is File Not Found.... Check that the Windows user that the SQL Server Agent uses has permissions on that file/folder.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]