Using WCF endpoints with SQL Reporting Services
Consuming WCF endpointpoints with SQL Reporting Services can be difficult. Most of this difficulty is for two reasons:
1) SQL Reporting Services XML and web services support is slightly better than a piece of crap.
2) Documentation for the XML query provider in SQL Reporting Services blows.
One of the big problems with SSRS is that you can't just point it to a WSDL document and have it generate a proxy. In fact, the way you use web service data is virtually the same as querying off a static XML document. However, it is possible to get the two working together. Here are the steps:
1) Expose your WCF service using BasicHttpBinding.
2) Select XML data source and enter the url of your service, ie. http://myserver.com/service.svc
3) Enter the XML for your query:
<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>
4) Make sure your WCF proxy is set up properly to work with SSRS:
[MessageContract(IsWrapped=true)]
public class MyMethodRequest
{
[MessageBodyMember]
public string Param1;
}
[ServiceContract]
public interface MyService
{
[OperationContract]
MyMethodResponse MyMethod(MyMethodRequest request);
}
Notice a few things. First, you will want your request to be wrapped. By default, the method name in your SSRS query must match the class name of your request. If you want to change your wrapper name, that is ok, but make sure to keep the wrapper name / namespace in sync with the Method element in SSRS. If you specify a custom action on your OperationContract, make sure that the soap action matches that action.
If you get an error message when you try to connect, remember these two things:
1) The error dialog has a details button where you can drill down and get more detailed information about the error that occured on the server, rather than the generic useless error it gives you.
2) If the details on the error dialog aren't giving you enough information, enable message tracing on your WCF service. If you aren't familiar enough with the WS specs or WCF to know what is wrong with the communication, create a quick app that calls the service using WCF and then compare a trace between the SSRS call and your sample app.
As a final note, watch out for missing or extra trailing slashes in namespaces and make sure everything is cased properly. One missing or extra slash or one improperly cased character can hose the entire request.