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 bindingvar binding = new BasicHttpBinding();//configure the bindingbinding.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 channelclient = 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 channelIClientChannel channel = (IClientChannel)client;
channel.Close();
//close the ChannelFactorychannelFactory.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
