DTSRUN error creating Excel Application

Question

DTSRUN error creating Excel Application

GN-328992

SSC Journeyman

Points: 99
More actions
May 16, 2006 at 5:06 pm

#68800

I am working a DTS package that saves and Excel Spreadsheet to a remote server. There are several global variables that are read in to the package. I have a .cmd file that contains a series of DTSRUN statements containing the variables. When I execute the package through DTS designer with static variables everything works fine. When I execute the xp_cmdshell test.cmd file I get the following error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_11
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_11
DTSRun OnStart: DTSStep_DTSActiveScriptTask_2
DTSRun OnError: DTSStep_DTSActiveScriptTask_2, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: ActiveX component can't create object: 'Excel.Application'
Error on Line 36
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: ActiveX component can't create object: 'Excel.Application'

I would think this would be a connection problem. However, I am using DTSRUNUI to generate the DTSRUN scripts from the successfully executed test package. Of note, when I open a Command line and type DTSRUNUI or DTSRUN I get the following error.
‘Windows Can not find DTSRUNUI’ or ‘Windows Can not find DTSRUN’
It is as if the op system does not see the files. Both .exe files or in the …SQL\80\tools\binn folder.

The xp_cmdshell 'set path' is
Path=C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Program Files\Common Files\Network Associates\VirusScan Engine\4.0.xx\;C:\Program Files\Dell\OpenManage\Array Manager;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\Program Files\Intel\DMIX
;C:\Program Files\Diskeeper Corporation\Diskeeper\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH

What am I missing???

Thanks for your help

Greg

Viewing 5 posts - 1 through 5 (of 5 total)

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

Recce SSC Rookie Points: 33 More actions · Answer 1

Howzit Greg,

I'm not very sure however, I am just making a suggestion. To me it looks like your virus scanner prevents you from starting any .exe's So, what I would suggest is for you to temporarily stop your virus scanner if possible and just check if it could be that....

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 2

Make sure that the machine this is running on has Excel installed on it. Also look at the processes running under Task Manager. If you see EXCEL.EXE, kill it and try again.

Mikkel Vous Kristiansen Newbie Points: 6 More actions · Answer 3

Hi,

I have had this problem and been around the internet to try and understand what is going on. My solution is now working after realising the following:

The process is run through DTSrun which changes the context that the package runs in. Excel is simply not registered in this context which is what generates the error.

To resolve it you need to make sure that excel can start up using the account that you want to use it for. This can be the service account or another account that you create for this purpose. When excel starts up, it will register itself.

On the taskbar, click Start , click Run , and then type DCOMCNFG and find excel under Component Services, Computers, <Computer>, DCOM Config.

Right-click to get the properties and enter the account in the Identity tab.

In the security tab, make sure that the account has permissions to launch and activate, Access and Configure as needed.

Hope it helps and good luck

Regards

Mikkel Vous Kristiansen

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 4

There's a funny behavior that I've noticed in DTS:

The same result as the ActiveX CreateObject can be obtained if you delete the Excel XLS file using the FSO deletefile method inside an ActiveX task, and then create the table in a Execute SQL task immediatedly following it.

This is assuming you, like me, only wish to repopulate the data ever time you run DTS. The table name in your create statement and transform destination must not use the '$'.

Somehow, DTS knows to recreate the XLS file that's needed for the Excel Connection object.

I was baffled when I discovered this worked very well on the server without Excel installed.