Unanswered: How to generate excel(.xls) file using VB script and load through DTS package?
I want to load the excel file with database data.
So i created a DTS package which transfers the data from database to excel(.xls) file.
**Since DTS accepts only .xls extension files**, i am planning to create .xls file only.
step1: create the excel(.xls) file with 3 columns(**using VB Script**).
step2: map these 3 excel columns with table colmns in transformation task.
step3: Load the excel
Now if i run the package, it is throwing error like **destination file is not in expected format.**
But file is generated with .XLS extension.
if i try to open the excel manually, it is also throwing the same error. But i am able to open the file.
If i do save as to the same file, it is showing the file type as .xlsx in the drop down. now i changed it to .xls in the drop down and saved . Now it is working fine.
**Why it is showing .xlsx file type even if we created .xls file?
Is there any solution to create pure .xls(**which will show .xls in save as**) file which can be used in DTS package?**
I have used the below VB Script to generate excel file.
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
oSheet.Range("A1").Value = "Column1"
oSheet.Range("B1").Value = "Column2"
oSheet.Range("C1").Value = "Column3"
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections(2)
oConn.datasource = D:\excel.xls
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success