If you have a report that always outputs to an Excel Destination, here’s a trick my team uses to always export the output the same way each time. We use an excel template to keep the output uniform.
- Run the output SQL first to get the data.
- copy/paste the output to Excel.
- Save the file with a generic, but related name. We keep our templates in a separate directory for safekeeping.
- Remove data, but keep headers.
- Create 2 variables in SSIS
- srcFile (source file / template)
- dstFile (destination file, using expression for datestamp)
- In the beginning of your SSIS package, use a and use the two previously created variables.
- What this step does is copy your template with headers to a new file location with a unique name (based on datestamp).
- In the Excel Destination step, create an expression in the properties.
- This will write your output from your run into the unique file that was just created, without modifying the original template.
Using this technique, you can use the template over and over again without having to manipulate the output data after the fact.