SSIS Custom Excel Creation

0
votes

Morning All,

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!

1
sqlexcelssis
So you dont want to consider a VBA script for this? – K_B
Problem is that I'm pretty dreadful at VB! – GPH

1 Answers

0
votes
  1. 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

A6: F1

B6: F2

B6: F3

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.