Error using variable for worksheet name in SSIS Excel destination

This is another quick post about something many of you already know about, using the property page in SSIS.

One of the things I monitor as a DBA is the space used by our databases on our production servers. I collect those stats on a weekly basis and store them in a central repository that I can query later. I’ve been saving the results for a while, and I’ve had different methods of getting the data out; queries, reports, even Powershell. But since I’m learning SSIS for Microsoft’s 70-448 exam I thought I’d look for a SSIS solution this time.

Getting the data out of the database and into an Excel spreadsheet is simple enough; a data flow task with an OLE DB source, a data conversion transform, and an Excel destination.

image

And that worked, to a certain point. I wanted to be able to write each package run to a new sheet. So I created 2 package variables; SheetName and SheetTable. SheetName has a value of Default. I used the New Table in the Excel destination to generate code for a new table, named my table Default, and placed that as the value of the SheetTable variable. In a script task on the control flow I set SheetName to a string that includes the date and inside the SheetTable variable replaced “Default” with the SheetName value. Then I use an Execute SQL Task to create the new sheet in my Excel spreadsheet.

My problem came back in the Excel destination, wen I tried to set the table to use the SheetTable variable. Everytime I tried I got a warning that the table didn’t exist. I made sure it did, as a template. I had DelayValidation set to True in the Excel connection manager. I couldn’t get it to save.

That’s when I went to the property page for the Excel destination. There I could set the individual properties with no errors. I set the AccessMode to OpenRowset From Variable and the OpenRowsetVariable to my SheetName variable.

image

And that’s all it took. Saving the package and running it now gives me a sheet for every day I run the package.

image