ActiveX DTS job doesn't run when using SQL Server Agnet Jobs Scheduler

[dk2006]

Hi all, I created an ActiveX inside DTS job package. It runs fine if I execute it manually. If I put inside the SQL Server Agent Jobs Scheduler, it returns an error 'ActiveX component can't create object 'Excel.Application'...

Here's code inside the ActiveX

Dim Excel_Application 
Dim Excel_WorkBook 
Dim Excel_WorkSheet
Dim rRange

Dim sFilename
Dim sSheetName

sFilename = "E:\Temp\US Active.XLS"			
sSheetName =	"Active"				
	
Set Excel_Application = CreateObject("Excel.Application")

' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
	' Find the WorkSheet specified
	If Excel_WorkSheet.Name = CStr(sSheetName) Then
		Set rRange = Excel_WorkBook.Worksheets(sSheetName).Range("A2:IV65536")
		rRange.Delete
		Excel_WorkBook.Save
		Exit For
	End if
Next
	
'Clean Up our Excel Objects
Set Excel_WorkSheet = Nothing
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing

Main = DTSTaskExecResult_Success

Thank you,
dk

 

[dk2006]

Other problem: "The Microsoft Jet database engine cannot open the file "E:\Temp\US Active.XLS"

WOnder why?

dk

 

[TomKane]

Hi,

Is excel installed on your server? That could be the problem. Take a look at this thread:

thread961-1307004

The author of this thread (JinjaNinjaUK) uses linked servers to get at excel data. This might be of some use to you.

Regards,
Tom

 

[dk2006]

Thank you TomKane, I found the problem with "The Microsoft Jet database engine cannot open the file "E:\Temp\US Active.XLS". The SQL Server Agent user account is different than the one I assigned as owner. This problem was fixed.

You are right on Excel not installed on the server. I talked to my Network Admin. He didn't really want to install MS-Excel on that server. I wonder if there's anyway that I can just install the Excel component only.

Anyone has any idea?

Thanks,
dk

 

[TomKane]

Hi,

I'm not completely sure. I think the ideal would be to get Excel installed on the computer.

What I did do was that I put your code into a VB6 exe and ran package and deployment wizard and it came up with the following dependcies:

VB6STKIT.DLL
COMCAT.DLL
stdole2.tlb
asycfilt.dll
olepro32.dll
oleaut32.dll
msvbvm60.dll

I think you should see if you can find a test machine that doesn't have Excel installed and see how many of the above dlls you need to make your DTS work.

Regards,
Tom

 

[dk2006]

Thank you guys.