Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.