ActiveX script throwing runtime error inside DTS package

Question

I am a DBA. We have migrated SQL 2000 databases + DTS packages to SQL 2008. One of the DTS packages runs an ActiveX script to open an Excel file (which has runs macros when you open it). Coding inside the ActiveX script is:

Function Main()    
    Set XlApp = CreateObject("Excel.Application")
    call XlApp.Workbooks.Open("\\server\c$\path.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path2.xls",3)
    call XlApp.Workbooks.Open("\\server\c$\path3.xls",3)
    set XlApp = nothing
    Main = DTSTaskExecResult_Success
End Function

Sometimes it throws "Active X component has experienced a run time error" without even telling the details about the error and sometimes it runs fine. The DTS package is run using a SQL agent job. Is there a better and reliable way to do this without using the ActiveX script?

Thanks!

Answer 1

Although I am NOT proficient with .net code, this particular example seems like a good fit to use .net or SSIS components listed below. You could even use PS along with SSIS.

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm

http://pragmaticworks.com/help/dtsxchange/scr/FAQ%20-%20How%20to%20convert%20Scripting.FileSystem%20object%20of%20ActiveX%20Script%20to%20native%20SSIS%20Task.htm

http://pragmaticworks.com/help/dtsxchange/index.htm#page=ActiveX Script Task.htm