Integration of SSIS with WCF Service

To use the SSIS configuration mechanism to configure a WCF client, the easiest way is to configure your WCF client in code. We can plug in package variables for things like the service Address, user credentials settings etc. 

Here’s a quick walk-through of calling a Service from a WCF client in an SSIS package, configuring the WCF client in code and using a package variable to insert the return value in database.

First create a WCF Service for testing (I’ve used the default WCF service code for implementation)


Here is the code snippet used to create a service:

Then hit F5 to run the service in the development server, and note the address of the .svc file:

Create a new SSIS package and add a Script Component configured to 
Source and a Execute SQL Task.  Add Package variable named “ReturnValue” to the script source to match the data flowing out of the web service.  Here it’s just a single String.

                                   

Edit the script component and pass the package variable to the script to get the output from the service.

Note: This variable will be available using suffix “User::” as it is a user defined variable not the system defined.

                                 

Now click on Edit Script button to edit the script component. 

It will open the script editor project. Edit the script for the script source 

and change the target .NET Framework from 2.0 to 3.5 so you can

use WCF:

In the Script project add a Service reference:We can follow the conventional 
procedure of consuming WCF service by creating a Proxy class also (using channel factory).

Now edit the script to configure the WCF client in code and Set the 
package variable with the output of the service.

If you’re not sure what binding to use look at the app.config that the 
Add Service Reference wizard put in your script project.

And here you go after running the SSIS package :)