September 1, 2005 at 8:43 am
I have a stiuation where I need to import data from Excel and of course I'm using DTS to do so. Because the spreadsheets have a variety of datatypes in them, I have to make sure that DTS recognizes certain rows as INT so they will import properly, to do so I add 5 rows of 1's to the top lines of the individual sheets in the correct columns. After the 1's are entered I import to staging tables and then into the production tables. The package itself is run from a job and the job is called by a stored procedure running sp_start_job. Here is a sample of the ActiveX script I am using to alter the spreadsheets:
*****************************************************************
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
Tim Conlan
NEW Database Develpoer
September 1, 2005 at 1:31 pm
While looking through the DTS Step Log, I saw that the specific error I am getting is: Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'Excel.Application' Error on Lin
Error Code: -2147220482
Again, thanks
Tim Conlan
NEW Database Developer
September 1, 2005 at 10:08 pm
Do you have Excel installed on the server that is running the DTS and ActiveX script? From your error is seems not.
Also Close the XLS between the ActiveX and DTS, as it will be locked by the ActiveX, so DTS cannot open it.
Finally does the SQL Agent and SQL Server service account have permissions to the XLS path?
Andy
September 2, 2005 at 4:11 am
If the Exell file is located on the network !
If SqlServerAgent is running on Localsystem account you do not have any access to the Network
If you run it in enterprisemgr the account that you use when you logged on to the sql server
will be used.
Try starting the Job from Enterprisemgr you should get the same error !
September 2, 2005 at 4:14 am
The solution is to place the File on the SQL server machine
but thats not the place you want it ?
You can change the Account for the SQL server agent to a Domain account !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply