Question
run a report weekly on Monday. There is a spreadsheet in which I change the Startdate and enddate only in the WeeklyMSfile.xls and worksheet is Weekly and column are A1 and A2. Suppose I am running report on Monday 07, 2008. I will put startdate as 06/30/2008 and Enddate will be 07/07/2008. After putting the date I run the executable file which is looking data from spreadsheet and I get the result in text format.
I just want to automate the spreadsheet to avoid weekly input data of startdate and enddate. My question is how can I create DTS-package so that when I run the DTS-package, automatically change the startdate and enddate and run the executable file.
Answer
Spreadsheet name is: WeeklyMSfile.xls
Executable file name is : WeeklyMS.exe
1. Import the Excel data into a #temptable
2. Change the columns names with sp_rename
3. Export the #temptable to Excel