SSIS Custom Excel Creation
I'm trying to come up with a solution to automatically update a excel report that's currently updated manually(SQL runs and copy and paste job). I've got a few idea's but i'm wondering if there is a way in SSIS that would manage it. Problem is I need to create a file with a specific format, logo at the top, text in line a2, report title a4 and then data a7, b7, c7 ect...
I need the file to update and save as a unique name on the 1st of each month, considered using a sql driven excel data source but then I would need to open, refresh and save. Don't really want to have a scheduled task to run on open and save vb script.
If anyone has any idea's that would be great!
- The source file (.txt) looks like this:
F1, F2, F3
1, A1, A2
2, B1, B2
The first line has the field names.
Create your template file. Name it ReportTemplate.xlsx. Create a copy (for now manually) Reort.xlsx
A1: Logo Image
A2: Your Text
A4: Reoprt Title
Make sure there is nothing below line 6.
Now when you connect your source to the destination file (Report.xlsx), SSIS will automatically write from A7.
Once this is done, you would have to create a script task to copy this template to a file and name that file according to your requirement. The connection string for the destination would need to be dynamic. If you need help, please revert back.