March 2, 2006 at 5:55 am
I am loading an excel file into sql table using sql DTS, which is a sheduled job. My job is looking for test.xls and that excel file has test20060301 as sheet1 and the name of the sheet1 will be changing every day according to date, because of which my sheduled job is failing to load. How can I resolve this so that my DTS job loads the file though the name of the sheet is changed.
March 2, 2006 at 11:10 am
Changing the sheet name dynamically often loses the transformations as well.
The easiest and most reliable way to get around this is to make sure you always import with the same sheetname.
You need to copy the excel file to a location where the dts package can pick it up. Then rename the sheet in activex code. That way dts has a consistent source as long as columns remain the same.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim appExcel
Dim newBook
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Open("c:\Book1.xls")
newBook.Worksheets(1).name = "asaaes"
With newBook
.save
End With
appExcel.quit
set appExcel = Nothing
set newBook = nothing
Main = DTSTaskExecResult_Success
End Function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply