DTS error - ActiveX component cant create object

Question

DTS error - ActiveX component cant create object

PurpleLady

SSCertifiable

Points: 7819
More actions
November 9, 2004 at 1:13 pm

#163620

I've created a dts package that uses 2 ActiveX tasks to 1) delete an existing copy of a spreadsheet and 2) create a new version of the spreadsheet (Excel 2000). I run each of the steps individually - everything's great! I run the dts package through EM, works fine... Schedule the job and get "ActiveX component can't create object: 'Excel.Application'"
What am I missing... I've check the permissions and agent settings - they all check out. There are other jobs that create an Excel speadsheet (albeit in a different way) and they work.
Here's how I delete the existing spreadsheet (so I don't have to confirm an overwrite):
Function Main()
    Dim oFSO
    Dim sDirectoryPath
    Dim oFolder
    Dim oFileCollection
    Dim oFile
    Dim iDaysOld
'Customize values here to fit your needs
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sDirectoryPath = "\\Asg2\PingAn\Exsel Spreadsheets"
    Set oFolder = oFSO.GetFolder(sDirectoryPath)
    Set oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
    For Each oFile In oFileCollection
        If oFile.Name = "International_ExSel.xls"Then
                oFile.Delete (True)
        End If
    Next
' Clean up
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFileCollection = Nothing
    Set oFile = Nothing
Main = DTSTaskExecResult_Success
End Function
Here's how I setup the spreadsheet:
Function Main()
Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn
Set appExcel = CreateObject("Excel.Application")
Set newbook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
' Specify the colum name in the Excel worksheet
oSheet.Range("A1").Value = "Year"
oSheet.Range("B1").Value = "Month"
oSheet.Range("C1").Value = "Day"
oSheet.Range("D1").Value = "Platform"
oSheet.Range("E1").Value = "Count"
' Specify the name of the new ExcelFile to be created
DTSGlobalVariables("FileName").Value = "\\Asg2\PingAn\Exsel Spreadsheets\International_ExSel.xls"
With newBook
  .SaveAs DTSGlobalVariables("FileName").Value
  .save
End With
appExcel.quit
' dynamically specify the destination Excel file
set oPackage = DTSGlobalVariables.parent

'connection 2 is to the Excel file
set oConn = oPackage.Connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function
I'd appreciate any help I can get. Thanks!

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

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

PurpleLady SSCertifiable Points: 7819 More actions · Answer 1

I was able to remove the ActiveX components and replace them with SQL tasks. I keep the same connections, etc... but now it works whether scheduled, run thru EM on the desktop, and/or run thru EM on the server. Still don't understand why it didn't work in the first place.

Jeet SSCommitted Points: 1536 More actions · Answer 2

If you run as a scheduled job, it's running on the server, in which case you need to have EXcel installed on the server. When you run the DTS paclage manuualy or through Em it runs on your Local machine and so if you have excel installed this is why it probably work.

Hope this helps.

JEET

Thanks Jeet

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 3

RitaBowman -- just wondering, how did you accomplish creating an Excel file via a SQL Task?

Thanks,

Brian

PurpleLady SSCertifiable Points: 7819 More actions · Answer 4

I setup an Excel connection and a database connection. I used the Excel connection to create a "table", and setup a SQL query within the transform data task properties to select the information to populate the table (which is actually the spreadsheet).

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 5

Oh, OK. I thought you meant that you created the Excel file from within a SQL Task.

Thanks