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