How can I create a SSIS package that reffreshes Excel document connected to SQL data. I need to then save the file to 2 locations using YYYYMM ending?
Posted on 2009-04-26
I run monthly reporting. SPs generate the reports into tables in SQL 2000 server.
I have a linked workbook that contains links to the report table in SQL.
Everymonth a SSIS package runs the SPs to generate the reports in SQL server.
I then manually go to each report and refresh all sheets in the workbook.
Then I save the template and then save the file into 2 locations.
Is it possible to do this task using SSIS to avoid the need to manually do this.
the report structures are:
TEMPLATE: REPORT YYYYMM.Xls
Final report: G:\...\Report 200903.xls
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
I also save some of the reports as PDF sheets.
Can this also be done using SSIS ?