DTS Package fails when scheduled but runs when manually run

Question

DTS Package fails when scheduled but runs when manually run

Chris Golla

Ten Centuries

Points: 1171
More actions
July 21, 2006 at 12:07 pm

#70917

Using SQL Server 2000 Data Transormation Services (DTS), I have created a package which imports data from an external database (Oracle 10G) via the OLE DB Provider for ODBC.
The package executes successfully when run manually (from within the DTS Package Designer or from the DTS Local Packages context menu).
The problem arises when the package is executed as a scheduled SQL Server Agent Job. Every time the job is executed, the following error is recorded in the Job History Log:
Code:
Executed as user: AMERICA\SVCSQL. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Specified driver could not be loaded due to system error 1114 (Oracle in OraClient10g_home1). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 160 (A0) Error string: Specified driver could not be loaded due to system error 1114 (Oracle in OraClient10g_home1). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
The America\SVCSQL account is an admin on the windows server and runs the sql server and agent. I can login manually as America\SVCSQL and successfully run the package.
Does anyone have any suggestions for resolving this problem?
July 24, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 25 total)

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

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

I'll take a guess. When you run the package manually (as yourself or as America\SVCSQL), are you logged on to a workstation or to the server? My guess is that the driver is on your workstation but not on the server itself.

Greg

Chris Golla Ten Centuries Points: 1171 More actions · Answer 2

I am logged onto the server itself and I can open the ODBC wizard on the server and select the Oracle 10G driver. Thanks for the reply though!!

Randy Doub SSCertifiable Points: 5322 More actions · Answer 3

I have had issues like this when the package is created on my client that is at a different service pack level than the server the package is saved on (and run on).

James Griffin-409498 SSC Journeyman Points: 75 More actions · Answer 4

I am having the same issue. I have create a DTS package on my server and can log onto that server and execute manually but when I try to run as a SQL Server job I get the same message:

Step Error Description:Specified driver could not be loaded due to system error 1114 (Oracle in OraClient10g_home1).

This has to be a bug with Microsoft. I am going to contact them now and let you know what the resolution is. I have created and Run many DTS packages but it seems this issues is only now showing when we moved to the Oracle drivers 10.X.XX

Chris Golla Ten Centuries Points: 1171 More actions · Answer 5

Chris Golla

Ten Centuries

Points: 1171

February 16, 2007 at 1:26 pm

#690320

Great!! Let me know what you hear...

tobias SSC Enthusiast Points: 140 More actions · Answer 6

A long shot, perhaps, but I am getting similar errors when accessing ODBC from ASP (not ASP.NET). My sense, is that somehoe it is security related...and I don't know what folder, what ttemp atrea, what... I need to open up to make the ORACLE ODBC work.

Perhaps knowing what security context SQL Agent is running in would be useful?

tobias SSC Enthusiast Points: 140 More actions · Answer 7

Never mind. I saw the information in the first post.

matt moriarty Valued Member Points: 69 More actions · Answer 8

I am having excatly the same problem. I have several DTS packages that are scheduled to run. If i kick these packages off manually, they complete just fine. If left for the scheduled agent to run them, they fall over with this error message.

I'm using an ODBC connection to Lotus Notes using the NotesSQL odbc driver.

Also, these packages have been running merrily for over a year and have only recently become an issue.

Any information on this would be very gratefully received.

Kev Eames SSC Veteran Points: 204 More actions · Answer 9

Hi I'm having the same issue. Did anyone ever find a resolution to this one?

Mark Inge Grasshopper Points: 13 More actions · Answer 10

Hi I'm also having the same issue.

Did anyone ever find a resolution to this one?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Check who the owner of the jobs are. Had an issue at my last employer with processes working manually, but failing as a scheduled job. I got around this by setting the owner to sa.

Just a thought.

Peter Fiagbe Grasshopper Points: 24 More actions · Answer 12

I had a similar problem with OBDC Client for oracle. The DTS package failed when I scheduled it, but run without issue when started manually.

Try restarting the client/server where the DTS package will be executed. Rebooting the server solved my problem.

I know I did not have an issue with security because I was logged into the server with the same account that sql agent uses, when I executed the DTS package manually.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 13

What O/S (e.g. Windows 2003 32-bit) and version of SQL Server (e.g. Standard X64) are you running?

Joe