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.
Hi,
I am still getting the error. Even if I create an excel file save it and then try I still get error please help
Thank You
Hi Vijay. I’m not sure why you still get the error. Can you post the message you receive?
Hi Kathy,The script task is slmipe and I included it below. It sets the sheetname and also builds the column list. In the SQL Task I set the SQLSourceType to Variable and the SourceVariable then becomes User::SheetTable. I can’t add a screenshot of the whole thing here, but I start with the script task, then the SQL task, then the data flow I showed in the original post.Public Sub Main() Add your code here Dim strDate As String = FormatDateTime(Now, DateFormat.ShortDate).ToString strDate = Replace(strDate, / , _ ) Dts.Variables( SheetName ).Value = DBSize_ strDate Dts.Variables( SheetTable ).Value = CREATE TABLE `DBSize_ strDate ` (`ServerName` NVARCHAR(50),`DBName` NVARCHAR(50), `Name` NVARCHAR(50), `LastRunDate` DateTime, `PriorRunDate` DateTime, `LastTotalSizeMB` Decimal(8,2), `PriorTotalSizeMB` Decimal(8,2),`TotalSizeDiff` Decimal(8,2),`LastUsedSpaceMB` Decimal(8,2), `PriorUsedSpaceMB` Decimal(8,2), `UsedSpaceDiff` Decimal(8,2), `LastFreeSpaceMB` Decimal(8,2), `PriorFreeSpaceMB` Decimal(8,2), `FreeSpaceDiff` Decimal(8,2)) Dts.Variables( SheetTable ).Value = Replace(Dts.Variables( SheetTable ).Value, Default , Dts.Variables( SheetName ).Value) Dts.TaskResult = ScriptResults.SuccessEnd Sub
Hey John,
Great post, referring to “Error using variable for worksheet name in SSIS Excel destination”
I wanted to put the sheet name as an expression in the sheet name variable rather then writing a script to hold the name but its not working. Would you be willing to share your sql task and script ? A screen shot of that flow also would be helpful.
Hi Kathy,
The script task is simple and I included it below. It sets the sheetname and also builds the column list. In the SQL Task I set the SQLSourceType to Variable and the SourceVariable then becomes User::SheetTable. I can’t add a screenshot of the whole thing here, but I start with the script task, then the SQL task, then the data flow I showed in the original post.
Public Sub Main()
‘
‘ Add your code here
‘
Dim strDate As String = FormatDateTime(Now, DateFormat.ShortDate).ToString
strDate = Replace(strDate, “/”, “_”)
Dts.Variables(“SheetName”).Value = “DBSize_” & strDate
Dts.Variables(“SheetTable”).Value = “CREATE TABLE `DBSize_” & strDate & “` (`ServerName` NVARCHAR(50),`DBName` NVARCHAR(50), `Name` NVARCHAR(50), `LastRunDate` DateTime, `PriorRunDate` DateTime, `LastTotalSizeMB` Decimal(8,2), `PriorTotalSizeMB` Decimal(8,2),`TotalSizeDiff` Decimal(8,2),`LastUsedSpaceMB` Decimal(8,2), `PriorUsedSpaceMB` Decimal(8,2), `UsedSpaceDiff` Decimal(8,2), `LastFreeSpaceMB` Decimal(8,2), `PriorFreeSpaceMB` Decimal(8,2), `FreeSpaceDiff` Decimal(8,2))”
‘Dts.Variables(“SheetTable”).Value = Replace(Dts.Variables(“SheetTable”).Value, “Default”, Dts.Variables(“SheetName”).Value)
Dts.TaskResult = ScriptResults.Success
End Sub
can u show me the execute SQL task the sql statement ??? i try what u have did before..it not work for the select variables part..it looks like the variable name must be the same as the excel sheet name that have been generate..if good u can show whole flow step by step process u do..it will be help alot..thank…
Hi Div3zZ
I’ve posted a step by step description of my whole package. Hope it helps.
http://www.pertell.com/sqlservings/archive/2011/11/excel-worksheets-from-variable-revisited/
still facing the error as “Error at Data Flow Task [Excel Destination 1 [70]]: Opening a rowset for ____ “