*****************************************************************
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
Well, I got the first problem above figured out. When I ran the package directly from Enterprise Manager, the ActiveX object was created on my local system and used Excel there to do what it needed to. When I tried to run the package on the server which didn't have Excel installed, my script failed. My new solution (as soon as it works) is to use WshRemote to run the script to alter the spreadsheets using a computer with Excel installed. The following script works when run from my PC but fails with the message "Error Source = Microsoft VBScript runtime error, Error Description: Permission Denied" when run as part of the DTS package called from a stored procedure via dtsrun:
--
Dim Controller, RemoteScript
Set Controller = CreateObject("WSHController")
Set RemoteScript = Controller.CreateScript("\\Server\Share\ExcelAdd1s.vbs", "MyLocalPC")
RemoteScript.Execute
Being new to the DBA world, I need some help with permissions. Is it that the SQL Server account doesn't have access to the shared drive, or to my PC? Where can I look to see what user is running the DTS package? Are there any questions that I'm not asking that I should be? Any help would be appreciated.|||When yuo run the package from EM directly your logged in account needs to have permission to the share. When it runs from an agent job the SQL Agent account must have permission to do the same thing, it must be to a UNC share and not a mapped drive
