Package won't run correctly when called
*****************************************************************
Function Main()
On Error Resume Next
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim sFilename
Dim sSheetName
sFilename = "\\Share\LCS Database Information - Data Test.xls"
sSheetName = "Experience Spreadsheet"
Set Excel_Application = CreateObject("Excel.Application")
Excel_Application.DisplayAlerts = False
' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)
Excel_WorkBook.Worksheets(sSheetName).Activate
FOR x = 2 TO 6 STEP 1
FOR i = 1 TO 5 STEP 1
Excel_WorkBook.WorkSheets(sSheetName).Cells(i,x).Value = 1
NEXT
NEXT
*************************************************************************
It does this to 3 sheets and then the import steps run.
Now, when I execute the package in Enterprise Manager it works exactly as I want. However when I run it through the stored procedure, the ActiveX doesn't run properly. My stored proc looks essentiall like this:
****************************************************************************
--Creat TEMP table for the DTS package to store values from the spreadsheet
EXEC pCreateTEMPTables
IF @@ERROR <> 0
BEGIN
SET @Segment = 1
GOTO sqlerror
END
--Run the DTS package that will pull data into temp tables so the individual --data points can be used to search up keys
EXEC msdb.dbo.sp_start_job
@job_name = 'LCSRunDTS'
EXEC more.procs
****************************************************************************
One thing about the spreadsheets is that they have links in them wanring messages get thrown up during the ActiveX script about updating the links. I tried to turn them off but one message, "One or more links can't be updated" which comes up if you click "Update" on the previous warning or supress warnings, just won't go away.
Any ideas? Thanks
