Import a Report into a Table

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.

1. Create and publish your report. In my case, it was a simple table with a single column with a running total.
2. 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
3. Script getting the XML data. Continue with Mark's instructions to use the WSDL to tell SSIS how to get the report data.
4. 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:
   Imports System
   Imports System.IO
   Imports System.Data
   Imports System.Math
   Imports Microsoft.SqlServer.Dts.Runtime
   
   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)
   
           File.Delete("<localpath>\clearshort.xml")
           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)
           stream.Close()
           Dts.TaskResult = Dts.Results.Success
       End Sub
   
   End Class
5. Import the data into SQL. Using an XML data source, import how you'd like, and enjoy!

0 Comments