January 19, 2010 at 9:32 am
Legacy DTS package migrated to SQL Server 2005 that is scheduled as a job fails in step that refers to the Excel object model with the error:
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft Office Excel
Error Description: Unable to get the Open property of the Workbooks class
SQL Server 2005 Enterprise Edition version 9.00.4053.00
The Operating System we are running under
Windows Server 2008 Enterprise
Version 6.0.6002 Service Pack 2 Build 6002
We log on to the server as sqlagent under the domain the machine is defined in.
This package runs without error when it is executed from SQL Server Management Studio within the Legacy Data Transformation Services.
The failing job is scheduled using the prescribed Operating System Command DTSRun
The step was migrated, along with the job, from SQL Server 2000 MSDB database and inserted into the corresponding tables in the SQL Server 2005 environment.
The actual DTSrun command is:
DTSRun /~Z0xF5E52E15B19E5BCA89AA9902974D9CA9D47F5241482E2BA4EED8A90F1FA25D7498BAE84A0515DCEF29407D182EEC7ED7443ED82DE5B1FCCF1F0925F4F0C0A40327ED36699CC89F1391DE749750B60C2AFB4479AA2377DC035F6E162DD92E723F5A3C83FE6256206C48E1DC6482E353877D81E6F7A4671A1FCE6CAC4BF9C3D20B076177BDD62C9F5E2B950C0D757454FF7FD26F
The job step is set up to run as SQL Agent Service Account which is the only account available.
The SQL Server Agent Service is running as the local System Account.
Excel was installed as part of the Microsoft Office 2003 suite along with Outlook.
Of note is that in the DTS package log when the DTS is executed as a job the log shows
Executed By: SYSTEM
When the DTS is run from SQL Server Management Studio within the Legacy Data Transformation Services the log shows
Executed By: sqlagent
January 19, 2010 at 10:18 am
We've had odd failures like this too. Seems that we get orphaned Excel sessions open on the server, and after too many are left open, DTS packages using Excel fail, with different errors.
If I kill those extra Excel sessions through task manager, the failed job runs fine.
Anyone know how to prevent the orphaned Excel sessions ?
January 22, 2010 at 12:50 pm
Microsoft Tech Support helped us find the answer:
Simply had to add this folder.
C:\Windows\SysWOW64\config\systemprofile\Desktop
Apparently in Windows 2008 this folder is not there.
January 22, 2010 at 12:52 pm
Oh. and to the person that keeps posting their question about orphaned excel tasks. This is not what the issue ever was. You need to find out what is causing the excel task to fail. This happens to us in testing when we have an error in the excel automation code. We just go into task manager and kill the orphaned excel tasks and fix the issue in the activeX script.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply