Post reply

DTS error - ActiveX component cant create object

  • 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!

  • November 10, 2004 at 9:21 am

    #529644

    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.

  • November 11, 2004 at 7:22 am

    #529784

    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

  • November 12, 2004 at 8:50 am

    #529943

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

    Thanks,

    Brian

  • November 12, 2004 at 8:57 am

    #529951

    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).

  • November 12, 2004 at 9:09 am

    #529955

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

    Thanks

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

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