Excel worksheets from variable – revisited

I’ve had a few comments on a post I wrote earlier, about using a variable to create and populate an Excel spreadsheet. This is how I did it, step by step.

Let me recap what I’m doing. I have 4 servers that I collect some statistics from weekly. In the package, I want to gather total and available drive space, database size used and free, and table size. That is done via SQL scripts on each server every Sunday morning. On Sunday evenings I run a package to pull the data into a central repository. Finally I manually run another package to send data from the last two weeks to a local Excel spreadsheet.

The full package isn’t very complicated. The whole workflow looks like this:

First, I set up an OLE DB Source connection and an Excel Destination. The Excel file already exists. Then I created two variables, one called SheetName and the other called SheetTable. They’re both at the package level (my package is called “DBCompare”) and both are strings.

Both script tasks are similar. Each changes the value of the SheetName variable and uses code to create a table on the new Excel spreadsheet. Each also has the 2 variables set as ReadWrite. This code is from the DBCompare script task.

The Execute SQL task is where I found the issue I discussed in my original post. I wasn’t able to set the Source Variable on the screen below, but I could using the properties page. Afterwards, it shows up correctly.