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.
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.
And that’s all it took. Saving the package and running it now gives me a sheet for every day I run the package.