How to use a WCF service as SSRS 2008 R2 datasource

This article explains how to use WCF services that can return Lists of type T as datasource and dataset for a reporting services report. this is one of the many articles you can find on the web, who will each tell theri version of the story.

In another blog post i explain How to create a WCF service that can be consumed by SSIS or SSRS

Walkthrough

Create a Report Server project with BI Development studio 2008 R2. In the example it is called ConsumeWCF.

In the solution explorer rightclick on Shared Data Sources and Add New Data Source

• Name: dsrc_SysAComm
• Type: XML
• Connection string: http://<insert your hostname>/SysAService/SysAComm.svc
• 
• click OK

In the solution explorer rightclick on Shared Datasets and Add New DataSet

• Name: dset_Devicelist
• Data source: dsrc_SysAComm
• query Type: text
• Query:
  

  <Query>

  <Method Name="GetDeviceList" Namespace="http://tempuri.org/">

      <Parameters>

          <Parameter Name="LocationCode"><DefaultValue>A123</DefaultValue></Parameter>

      </Parameters>

  </Method>

  <SoapAction>http://tempuri.org/ISysAComm/GetDeviceList</SoapAction>

  </Query>

  
  
• To design and test the query, you can click the ‘Query Designer’ button. Note that the design feature is no more than a notepad-style text editor, but at least you can run the query to test for any syntax error.
  
  
• Save this shared dataset. It is now ready to be used as a dataset for a new blank report

SSRS Query syntax short explanation

Use the WCF Test Client to get some indication of the necessary key words. Personally I did not even try to use namespaces other than the standard tempuri.org. If you do not have the source code of the WCF service, then you can start WcfTestclient from the Developer command prompt for VS 2012. If you only have BI development studio 2008 and no VS2012, you can download a free version of SoapUI, which can be used like WcfTestclient

For starters have a look at the WCF Test client screenshot of the WCF service:

• <Query></Query> start and close the Query
  
• <Method> define the Name, Namespace (http://tempuri.org/ !mind the ending slash sign) and the parameters
  
  
  
  • <Method Name=”GetDeviceList” Namespace="http://tempuri.org/"> … parameters … </Method>
    
    
    
    • specify the name of the parameter
      
    • <Parameters><Parameter Name=”LocationCode”> …optional default value … </Parameter>
  
  
• <SoapAction> this single line is built with the Method Namespace, Interface service name and method name.
  
  
  
  • <SoapAction>http://tempuri.org/ISysAComm/GetDeviceList</SoapAction>

Create a WCF service to return List of type <T> and to be used by SSRS or SSIS

Problem

Data on system A can only be read by a proprietary interface. The data must be used by applications on system B equipped with SQL Server 2008 R2. The data will be read by SSIS packages to store in system B for further processing, and by SSRS for reporting.

Solution

To have a uniform access method, the data from system A will be passed along by a WCF service.

The solution will be made with Visual Studio 2012. Care must be taken that SSRS and SSIS can read the service data correctly. Therefore WCF service will use basic Http binding.

Other apps developed with VS 2012 can take full advantage of the List<Type T> returned by the WCF service. Odata has not been chosen since no viable solution to read Odata in SSRS has been found. Type T classes will be defined as MessageContracts in the service.

Walkthrough

• Create a new project WCF Service Application with Visual Studio
  • Under languages – select Visual C# - WCF – WCF Service Application
  • Call the project SysAService (the solution has the same name)
  •  
• Add a reference to the proprietary library with which to communiciate with System A.
  • this can be your own dll library too.
• Rename the Interface IService1 to ISysAComm and the service to SysAcomm. Rename the svc and the cs file too.
  
• · In the case of the proprietary library of System A , we had to change the Target framework in Properties of the application to .NET Framework 3.5
• · In Web config, verify that basicHttpBinding in the service endpoints definition
  

  <system.serviceModel>

  <services>

  <service name="SysAService.SysAComm" behaviorConfiguration="SysAService.SysACommBehavior">

  <!-- Service Endpoints -->

  <endpoint address="" binding="basicHttpBinding" contract="SysAService.ISysAComm">

  
  
• · In the Interface replace the default code by a method to retrieve a list of data from system A
  
  
  
  • the method is called GetDeviceList
    
  • the method returns a List<MyDevice>
    
  • the class MyDevice is also defined in the ISysAComm.cs file
    
    
    
    

    namespace SysAService

    {

    [ServiceContract]

    public interface ISysAComm

    {

    [OperationContract]

    List<MyDevice> GetDeviceList(string LocationCode);

    }

    [MessageContract(IsWrapped = true)]

    public class MyDevice

    {

    [DataMember]

    public string Abbreviation { get; set; }

    [DataMember]

    public string Designation { get; set; }

    [DataMember]

    public int Id { get; set; }

    public MyDevice()

    {

    }

    public MyDevice(string abbreviation, string designation, int id)

    {

    Abbreviation = abbreviation;

    Designation = designation;

    Id = id;

    }

    }

    }

  
  
• Now that we have the Interface to read and the class defined, edit the cpode in the service class SysAComm.svc
  
• Add a using statement to use with the soapexception
  
  
  
  • using System.Web.Services.Protocols;
  
  
• Add ServiceBehaviour before the class SysAComm
  
  
  
  

  [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

  public class SysAComm : ISysAComm

  
  

   

  
  
• Create the method GetDeviceList which will read the proprietary data and create a list of objects of type MyDevice.
  
• The complete list of SysAcomm.svc code
  
  
  
  

  using System;

  using System.Collections.Generic;

  using System.Linq;

  using System.Runtime.Serialization;

  using System.ServiceModel;

  using System.ServiceModel.Web;

  using System.Text;

  using System.Web.Services.Protocols;

  namespace SysAService

  {

      [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

      public class SysAComm : ISysAComm

      {

          public List<MyDevice> GetDeviceList(string LocationCode)

          {

              try

              {

                  SysAReader sys_a_reader = new SysAReader();   // the proprietary librra

                  List<SysADevice> sys_a_devices = sys_a_reader.GetDeviceList(LocationCode);

                  List<MyDevice> my_devices = null;

                  foreach (var sys_device in sys_a_devices)

                  {

                      if (my_devices == null)

                          my_devices=new List<MyDevice>();

                      my_devices.Add(new MyDevice(sys_device.Abbreviation, sys_device.Designation, sys_device.Id));

                  }

                  return my_devices;

              }

              catch (Exception ex)

              {

                  SoapException se = new SoapException(ex.Message, SoapException.ClientFaultCode, ex);

                  throw ex;

              }

          }

      }

  }

Debug the service

• Debug the service with the WCF Test Client : select the service SysAcom.svc in the solution explorer and hit F5.
  
• The WCF Test client starts:
  
  
• Doubleclick the GetDeviceList() method under ISysAComm, the Request and Response panes open.
  
  
• In the Request pane, the Parameter LocationCode has value (null), we can give a valid value to return a List of type MyDevice.
  
  
• When we click on the Invoke button, the method is called with the argument A123, click OK when the Security Warning dialog box opens.
  
• The Response pane is filled with data, in this case a list of 14 elements
  
  
• What we will need to call this service from a SSRS report or SSIS package can be found in the XML tab.
  
  
• Close the test client window.

Publish the WCF Service application to an IIS server

• In the visual studio project, rightclick on the SysAService project and select Publish… from the dropdown.
  
• The Publish Web wizard appears, select <new profile…> from the combobox.
  
  
• In the New profile dialog box enter a profile name, in the example we use the name of the IIS server
  
  
• Connection
  
  
  
  • Publish method: Web Deploy (make sure the web deployment agent is installed on the iis server
    
  • Server: TestWebServer (the test server is an intranet server in the Windows domain, it has no domain extensions)
    
  • Site name, the site will be published under the ‘root’ folder
    
  • User name and password: use an account with administrative rights on the iis server
    
  • Destination URL: http://TestWebserver/SysAService
    
  •  
    
  • Click on the ‘Publish’ button
    
  • The publish actions are executed and an I.E. windows is opened with the Destination URL.
  
  
• If you get HTTP error 500.19: most likely the application pool used is not for.NET 3.5
  
  
  
  • Start IIS Manager and connect to the TestWebServer
    
  • Open the branch under sites / root / SysAService
    
    
  • In the right hand side pane, click on Basic Settings… to open the Edit application dialog.
    
    
  • Click on the ‘Select…’ button to select an application pool with the correct .NET Framework. In our case we use .NET Framework 3.5, so we select a pool with .net Framework 2.0.
  
  
• When the app pool is adjusted, refresh the I.E. where you had the error. You should see the directory of the webservice
  
  
• When we click on the link SysAComm.svc the SysACom service page should display
  
  

  Conclusion

  
  

  The WCF service is now ready to be consumed by a Reporting services report or by an Integration Services package (both version 2008 R2). Read my blog about How to consume a WCF service in a source script in SSIS

Using WCF service in a Source Script Component with SSIS 2008

Problem

Given the URL of a WCF service that returns a set of entities as a List object, the list must be read by SSIS to update or insert into a SQL server database table.

Solution

• The SSIS package will use a Source Script component in a Data Flow Task to use the WCF service.
• The URL of the WCF is stored in a variable.The value of the variable can then be stored in a package configuration file, to ease deployment between development and production environment.
• The Source Script Component in the Data Flow Task will use the URL variable as readonly variable to set the web service address in the code.
• SOURCE: The WCF method is GetDeviceList with one argument, a specific code. The List returned by the WCF service is of type MyDevice with 3 fields:
  • Id: int32
  • Abbreviation: unicode string 50
  • Designation: unicode string 255
• DESTINATION: the data will be stored in a SQL db table:
  

  CREATE TABLE [dbo].[Device](

      [DeviceId] [int] IDENTITY(1,1) NOT NULL,

      [ExternalId] [int] NOT NULL,

      [Name] [nvarchar](255) NULL,

      [ShortName] [nvarchar](50) NULL,

   CONSTRAINT [PK_dbo.Device] PRIMARY KEY CLUSTERED 

  (

      [DeviceId] ASC

  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

  ) ON [PRIMARY]

• Source to Destination mapping
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  WCF list element	SQL table row
  Id	ExternalId
  Designation	Name
  Abbreviation	ShortName

Step by step

1. In VS 2008 add a new package to an Integration Services project.
   
2. in the package create a new variable UrlWcf of type string and set it to the Url of the service in the development environment, i.e. http://localhost/SysAComm.svc
   
3. Add a Data Flow Task to the package.
   
4. In the Data Flow add a new Script Component.
   
5. The ‘Select Script Component Type’ pops up. Select Source and click OK
   
   
6. Double-click the Script Component to edit it.
   
7. In the editor dialog window under the Script tab, in the ReadOnlyVariables select User::UrlWcf
   
   
8. Click On the Inputs and Outputs to define the Output and columns. The Output 0 is renamed to OutputDevice and 3 output columns have been added:
   
   
9. Click on Script in the lefthand pane. Click on the ‘Edit script’ button to edit the code.

Add WCF service reference

1. In the Project Explorer window, Edit the project properties, to change the Target Framework from 2.0 to 3.5.
   
   
2. Right-click on References and select ‘Add Reference'…’ to add the ‘System.ServiceModel’ reference.
   
3. right-click on References and select ‘Add Service reference…’
   
   
4. In the ‘Add Service Reference’ dialog window, type the URL and click on the Go button. If the service url was spelled correctly, the service is displayed, enter the Service reference Name ‘SysAComm, then click on the ‘Advanced…’ button.
    
5. Uncheck ‘Always generate message contracts’, Change the collection type: to System.Collections.Generic.List. click OK.
   
   
6. Click OK again to add the service reference.
   
7. The new servicereference is shown in the Project Explorer, and the System.ServiceModel reference is automatically added to References.
    

Edit the code in main.cs

1. add using statements




1. using System.ServiceModel and


2. one for the web service reference (the SC_… depends on the projectname that is generated).


3. using System.Collections.Generic; (to consume the List<> returned by the WCF service)
   


2. In the ScriptMain class add a ChannelFactory and a SysAComm declaration
   


3. 
   
   
   

   using System.ServiceModel;

   using SC_6dd47e9530f147da8d878d53a1a62376.csproj.SysAComm;

   [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

   public class ScriptMain : UserComponent

   {

       ChannelFactory<ISysAComm> channelFactory;

       ISysAComm client;

   
   
   
   
   


4. In the PreExecute code, create an instance of the ChannelFactory and client
   
   
   

   public override void PreExecute()

   {

       base.PreExecute();

       try

       {

           //create the binding

           var binding = new BasicHttpBinding(); 

           //configure the binding

           binding.Security.Mode = BasicHttpSecurityMode.None;

           binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.None; 

           var endpointAddress = new EndpointAddress(this.Variables.UrlWcf);

           channelFactory = new ChannelFactory<ISysAComm>(binding, endpointAddress);

           //create the channel

           client = channelFactory.CreateChannel();

       }

       catch (Exception ex)

       {

           byte[] bt = null;

           this.Log(ex.Message, 0, bt);

       }

   }

   
   
   
   
   
   
5. 
   

   In the PostExecute method, add code to close the channel.
   

   
   
   
   

   public override void PostExecute()

   {

       base.PostExecute();

       try

       {

           //close the channel

           IClientChannel channel = (IClientChannel)client;

           channel.Close();

           //close the ChannelFactory

           channelFactory.Close();

       }

       catch (Exception ex)

       {

           byte[] bt = null;

           this.Log(ex.Message, 0, bt);

       }

   }

   
   
   
   
6. Add the code to read the service and populate the script ouputbuffer in the CreateNewOutputRows method
   
   
   
   

   public override void CreateNewOutputRows()

   {

       try

       {

           List<MyDevice> my_devices = client.GetDeviceList("A123");

           foreach (MyDevice my_device in my_devices)

           { 

               OutputDeviceBuffer.AddRow();

               OutputDeviceBuffer.Abbreviation = my_device.Abbreviation;

               OutputDeviceBuffer.Id = my_device.Id;

               OutputDeviceBuffer.Designation = my_device.Designation;

           }

       }

       catch (Exception ex)

       {

           byte[] bt = null;

           this.Log(ex.Message, 0, bt);

       }

   }

 

Build and save all of the script. close the script window and save the Script component.

Use the output from the Script Component

The output from the script component is now ready for use, e.g. as input for a Slowly Changing Dimension

The final Data Flow looks like this

Conclusion

SSIS can be setup to handle WCF services that return List<T> objects in a Source script component.

I have created a blog about How to create and deploy a WCF service to be consumed by SSIS