Consuming WCF services with Reporting Services

by Sander

When looking to consume a WCF service in SQL Server Reporting Services (SSRS) I came across the XML datasource and a lot of articles showing me that while it is possible, it is a bit of a hassle with formatting the query and figuring out exactly which SOAP action to use and in what namespace.

Thats why I thought to have a go at creating a data source for SSRS that truly consumes a WCF service and would give me better metadata, like which fields are in the result set, which parameters the SOAP operation needs etc. So less of this:

<Query>
  <Method Name="MyMethodRequest" Namespace="http://tempuri.org/">
    <Parameters>
      <Parameter Name="Param1"><DefaultValue>ABC</DefaultValue></Param>
    </Parameters>
  </Method>
  <SoapAction>http://tempuri.org/MyService/MyMethod</SoapAction>
</Query>

but instead just:

MyMethod

And let the extension figure out the rest for you.

I dug around on MSDN and found a quite comprehensive article about writing a custom Data Provider Extension (DPE) that provides a walkthrough on how to create such an extension. It didn't take me all that long to create my own extension and have it generate WCF clients at runtime. Easy as pie you'd say.

Well.. not really. Deploying should be as simple as dropping the assembly in the correct location and editing the SSRS config file, but when I reloaded the web UI it didn't show my extension and the event log was riddled with errors. Yikes!

After a bit of digging it turns out that SSRS 2012 (the version I'm targeting) does not support the .Net 4.0 runtime but instead is based on .Net 2.0. Fortunately it did get an update so that .Net 3.5 is supported so we're good to go: WCF is supported in that version.

Having rebuilt the project against v3.5 I deployed the extension again but alas, the extension was still not visible. It took me a while to figure it out but I had switched the architecture from AnyCPU to x86 and that prevented the extension from loading. After I fixed that it worked like a charm!

I have put the sources for the extension up on GitHub so that other people looking to use WCF services in SSRS can use it as well. Have a look here for details on how to deploy and use the extension.