sql server dts: I have a DTS package that extracts some data to Excel worksheets Total_Data
and Total_Count then runs ActiveX script to open the Excel workbook and run a
macro to complete formatting the data.

I receive intermittent errors with the package:
Step 'DTSStep_DTSActiveScriptTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description: ActiveX Scripting encountered a Run Time Error Step
Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID: 4500

The server holds the Excel file and I must do End Process Tree on Excel.exe
via Task Manager before package can be run again.

Here is my script code:
Function Main()
Dim obj_IM_ExcelWB
Dim str_ClientInterval, str_ClientName
Dim str_Daily_Year_Abbr, str_Daily_Suffix, str_DeptName
Dim str_FileNameReport1, str_FileNameTemplate1
Dim str_FilePathNew, str_FilePathRoot
' Only do the following if global variables set in the package
If Not isNull(DTSGlobalVariables("gv_FilePathRoot").Value) Then
' Pick up global variables needed
str_ClientInterval = DTSGlobalVariables("gv_Client_Interval").Value
str_ClientName = DTSGlobalVariables("gv_Client_Name").Value
str_Daily_Year_Abbr = DTSGlobalVariables("gv_Daily_Year_Abbr").Value
str_Daily_Suffix = DTSGlobalVariables("gv_Daily_Suffix").Value
str_DeptName = DTSGlobalVariables("gv_Dept_Name").Value
str_FileNameReport1 = DTSGlobalVariables("gv_Report_File1").Value
str_FileNameTemplate1 = DTSGlobalVariables("gv_Template_File1").Value
str_FilePathRoot = DTSGlobalVariables("gv_FilePathRoot").Value
str_FilePathNew = DTSGlobalVariables("gv_FilePathNew").Value
'Create Excel.Application object then open Template file
Set obj_IM_ExcelWB=CreateObject("Excel.Application")
obj_IM_ExcelWB.Workbooks.Open str_FilePathRoot & str_FileNameTemplate1
' Run CreateValidationColumn macro in report file to complete report
obj_IM_ExcelWB.DisplayAlerts = False
obj_IM_ExcelWB.Run "CreateValidationColumn"
'Save Template as Report file with Date suffix
obj_IM_ExcelWB.ActiveWorkbook.SaveAs str_FilePathNew & str_ClientName &
"_SS_" & str_ClientInterval & "_" & _
str_Daily_Year_Abbr & str_Daily_Suffix & ".xls"
'Quit Excel.Application and free object
obj_IM_ExcelWB.Quit
Set obj_IM_ExcelWB=nothing
End If 'If Not isNull(DTSGlobalVariables("gv_FilePathRoot").Value) Then
Main = DTSTaskExecResult_Success
End Function

If I turn on just-in-time debugging and the package fails, I receive a
messagebox
An exception of type 'unknown exception' was not handled.
then the debugger points to the line after the Open command.

Here is the code in the macro
Sub CreateValidationColumn()
' Create working variables
Dim lngTotalColumns, lngTotalRows As Long
Dim str_Address, strRangeforColumn As String
' Gather range data for Issue Type column
Sheets("Total_Data").Select
Range("A1").Select
Selection.End(xlToRight).Select
lngTotalColumns = ActiveCell.Column
Range("A1").Select
Selection.End(xlDown).Select
lngTotalRows = ActiveCell.Row
' 65536 is maximum number of rows; indicates an empty sheet
If lngTotalRows = 65536 Then
Exit Sub
End If
' Copy Validation2 Worksheet onto Total_Data Worksheet in another area
Sheets("Validation2").Visible = True
Sheets("Validation2").Select
Range("A1:A2").Select
Selection.Copy
Sheets("Total_Data").Select
Range("CZ1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Validation2").Visible = False
' Set BreakFix/IMAC range to data rows
Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -3).Select
strAddress = ActiveCell.Address
strRangeforColumn = strAddress & ":$" & Mid(strAddress, 2, 2) & "$" &
lngTotalRows
Range(strRangeforColumn).Select
' Add Validation2 Range to BreakFix/IMAC rows with data
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$CZ$1:$CZ$2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
' Copy ValidationData Worksheet onto Total_Data Worksheet in another area
Sheets("ValidationData").Visible = True
Sheets("ValidationData").Select
Range("A1:B9").Select
Selection.Copy
Sheets("Total_Data").Select
Range("DD1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("ValidationData").Visible = False
' Set Issue Type range to data rows
Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -1).Select
strAddress = ActiveCell.Address
strRangeforColumn = strAddress & ":$" & Mid(strAddress, 2, 2) & "$" &
lngTotalRows
Range(strRangeforColumn).Select
' Add ValidationData Range to Issue Type rows with data
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$DD$2:$DD$9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
If lngTotalRows > 2 Then
Selection.FillDown
End If
Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -2).Select
strAddress = ActiveCell.Address
strRangeforColumn = strAddress & ":$" & Mid(strAddress, 2, 2) & "$" &
lngTotalRows
Range(strRangeforColumn).Select
' Add VLOOKUP Upstreamable Data from Issue Type
ActiveCell.Formula = "=VLOOKUP(BX2,$DD$2:$DE$9,2,FALSE)"
If lngTotalRows > 2 Then
Selection.FillDown
End If
' Call macro to Refresh All PivotTable caches
RefreshAllPivotTables
' Set Daily Overview as current worksheet
Sheets("Daily Overview").Select
Range("A1").Select
End Sub

Sub RefreshAllPivotTables()
' RefreshAllPivotTables
' Macro created 02/07/2007 by Margaret Young (Modis)
'
' Create working variables
Dim intPivotCacheCount As Integer
Dim intI As Integer
' Get the number of PivotCaches (memory from PivotTable reports)
' in the collection
intPivotCacheCount = ActiveWorkbook.PivotCaches.Count
' Loop thru PivotCaches, updating each Chart and PivotTable cache
For intI = 1 To intPivotCacheCount
ActiveWorkbook.PivotCaches(intI).Refresh
Next intI
End Sub

The global variables contain full path and file name i.e.

Turns out the error was caused by the pivot tables set to refresh-on-open.
Once I unchecked that setting, the packages no longer fail.

Thanks.
Margaret Young
Modis

[quoted text, click to view]