Thursday, March 5, 2009
Data naturally flows from database tables into reports - but what about the other way around? There are a few things that SQL isn't very good at, and running totals is one of them. You have to be be very particular and get everything lined up "just so," and solutions either are slow, or use less-than-desirable cursors.
But, hey, reports are really good a totals and such, and SSRS lets me grab any report as XML, so maybe I can get to it with SSIS? Turns out it's not quite as simple as I thought, but still very doable (thanks, Mark Garner). The problem is that SSIS doesn't want to read SSRS's WSDL.
- Create and publish your report. In my case, it was a simple table with a single column with a running total.
- Get the WSDL. Since SSIS doesn't like SSRS, you have to run a simple command once to get it. Depending on whether you're running SSRS standalone or integrated with Sharepoint, the endpoint will be different. [Note, you'll need to make sure the year number matches your version of SQL/Sharepoint]. Also, Mark was using a management endpoint; to actually run the report, use the execution endpoint.
Mine looks like this: wsdl.exe /language:VB /out:ReportExecution.vb http://<servername>/Reporting/_vti_bin/ReportServer/ReportExecution2005.asmx?wsdl
- Script getting the XML data. Continue with Mark's instructions to use the WSDL to tell SSIS how to get the report data.
- Save the XML where you can get it. I tried saving the XML to a variable in SSIS, but that didn't want to work. So, instead, I saved it out to and XML file on the local drive, like this:
Public Class ScriptMain
Public Sub Main()
Dim enc As New System.Text.UnicodeEncoding
Dim warnings As Warning() = Nothing
Dim streamIDs As String() = Nothing
Dim rs As New ReportExecutionService
Dim result As Byte() = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LoadReport("http://<servername>/Reporting/Test reports/ClearShort.rdl", Nothing)
result = rs.Render("XML", "", "", "", "", warnings, streamIDs)
Dim stream As FileStream = File.Create("<localpath>\clearshort.xml", result.Length)
'Dts.Variables("xmlReport").Value = enc.GetString(rs.Render("XML", "", "", "", "", warnings, streamIDs))
stream.Write(result, 0, result.Length)
Dts.TaskResult = Dts.Results.Success
- Import the data into SQL. Using an XML data source, import how you'd like, and enjoy!