Jan D'Hondt: 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

In VS 2008 add a new package to an Integration Services project.
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
Add a Data Flow Task to the package.
In the Data Flow add a new Script Component.
The ‘Select Script Component Type’ pops up. Select Source and click OK
Double-click the Script Component to edit it.
In the editor dialog window under the Script tab, in the ReadOnlyVariables select User::UrlWcf
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:
Click on Script in the lefthand pane. Click on the ‘Edit script’ button to edit the code.

Add WCF service reference

In the Project Explorer window, Edit the project properties, to change the Target Framework from 2.0 to 3.5.
Right-click on References and select ‘Add Reference'…’ to add the ‘System.ServiceModel’ reference.
right-click on References and select ‘Add Service reference…’
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.
Uncheck ‘Always generate message contracts’, Change the collection type: to System.Collections.Generic.List. click OK.
Click OK again to add the service reference.
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

add using statements

using System.ServiceModel and

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

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

In the ScriptMain class add a ChannelFactory and a SysAComm declaration

```
using System.ServiceModel;
```
```
using SC_6dd47e9530f147da8d878d53a1a62376.csproj.SysAComm;
```
```
 
```
```
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
```
```
public class ScriptMain : UserComponent
```
```
{
```
```
    ChannelFactory<ISysAComm> channelFactory;
```
```
    ISysAComm client;
```

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);
```
```
    }
```
```
}
```
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);
```
```
    }
```
```
}
```

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

Jan D'Hondt

Thursday, February 13, 2014

Using WCF service in a Source Script Component with SSIS 2008