Tech Talk

Permanent link to Import a Report into a Table 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)

            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
        End Sub

    End Class
  5. Import the data into SQL. Using an XML data source, import how you'd like, and enjoy!


LaRocque Family