SSIS Excel destination: Templates!

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.

template_dir * directory

template_example * filename

  • Remove data, but keep headers.

template_ex_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 filesystask and use the two previously created variables.
    • filesysvars
    • 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.
    • exceldstexpr.PNG
    • 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.

 

SSIS Variables

For a number of projects I work on, I make use of Microsoft’s Integration Services (SSIS) solution to run some queries and drop output into a formatted Excel file for our various end users. I’ve only been using SSIS for a little under a year, so I know there’s lots to learn.

One trick I’ve found to be very handy is the use of Variables in the the packages.  Specifically for this post, I’m focusing on using the expression to make the variable dynamic and using that value to output a file and change the name.

After starting the project, create a variable, making sure that the scope is for the package.
var1

Click the ellipses next to the line you want to create an expression for to bring up the Expression Builder.
var2

In the Expression text box, use the location of the output file (local or network) and concatenate with a date to keep it fairly unique.
var3

!!! One important item to note is that if your package works across the network, you will need to be wary of the escape character. When building the expression, you’ll need to double-up on the ‘\’ for each one used. For example, if you’re output directory is \output, the package will interpret it as “\o”. That’s bit me a few times before I figured out to use “\\output”. See the pic above to see what I mean.

Once you’ve decided on the value for the expression, click ‘OK’ and then the variable will use whatever the calculated value is.

In SSIS, I’ve typically used this for naming the products of the ‘Excel Output’ task [I will detail that in another post]. The variable can be used in the “File System Task” object as the ‘Destination Variable’ or ‘Source Variable’
var4
var5

If the path ever needs to change, you only have to update the variable.