Thursday, February 13, 2014

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 elementSQL table row
    IdExternalId
    DesignationName
    AbbreviationShortName

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
    image
  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
    image
  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:
    image
  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.
    image
  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…’
    image
  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.
    image 
  5. Uncheck ‘Always generate message contracts’, Change the collection type: to System.Collections.Generic.List. click OK.
    image
  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.
     image

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
image


The final Data Flow looks like this
image


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

No comments: