How to call a WCF service using web service task in SSIS ? WCF and SSIS 2005

A day back I had a requirement where I had to write a simple web service which generates a Excel Report and had to get it executed as a daily job . So I thought of writing a SSIS package which will call a web service . But I am much into learning WCF ( Windows Communication Foundation ) these days .So I thought why dont I create a WCF service instead of Web service !! So I ended up creating a WCF service.

Now the question is Can I call a WCF service using Web service task in SSIS 2005 ?

The answer is Yes you can . All you have to do is follow the steps below .

Before we create a package , we need to make few changes to the Windows Communication foundation host and client so that we can make things work .

Make the following changes to the web.config of WCF host :

There will be a entry called as Bindings in the System.servicemodel tag in web.config of WCF host . You have to change the type of bindings in it as follows .
<binding name="BasicHttpBinding_ReportService"
maxNameTableCharCount="2147483647" />
<security mode="None" />

By default it will be wsHttpBindings in a WCF service . But in a web service it used to be basic Http Binding and web service task has been configured to work only with basic HTTP binding in SSIS 2005 . Since WCF service supports communication over different protocols , we can always set bindings and achieve the required functionality .

If your web service is also consumed by your web application or something , then you need to configure its web.config as below to enable it use the same binding (BasicHttp) which host exposes .

<basicHttpBinding >
<binding name="BasicHttpBinding_MPCService"
maxNameTableCharCount="2147483647" />
<security mode="None">
<transport clientCredentialType="None" proxyCredentialType="None" realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />

If you observe the web.config entries , you can observe maxDepth ,maxContentstringLength and other attributes being set to 2147483647 . This has been set as my output of webservice (Excel file ) was very large . You can remove them if you want .

Now lets create a SSIS package using BIDS 2005 .
  • The first thing you have to do is setup your HTTP connection using New Connection Wizard .
  • Now save the WSDL of the service in a local path . You can get the WSDL by using the link you have used as server URL in the HTTP connection manager above . The link will be something like http://localhost/WCFservice/ReportService.svc?wsdl.Save it as [Filename].wsdl .
  • Now add a web service task from the toolbox of your Business Intelligence development studio ( BIDS ) .
  • Open the Properties of your webservice task and configure it as below.

  • Web Service Task Configuration :In the HTTP connection , select the HTTP connection you added above .
  • In the WSDL File field , give the local path to where you have stored your WSDL file .
  • Set OverwriteWSDLFile to true and click on download WSDL .
  • Now click on input in the left side pane .
  • In the input screen you can select your service and the corresponding Web methods .
  • Select the required one and go to output option .
  • In the output screen, you can set output type to either some variable or file connection as per your requirement .
  • Now click on OK and you are done with configuring your SSIS package to call a WCF service .
Now you execute your task and you can see it working . If you still have any problems feel free to contact me .

For more solutions to day to day ASP.NET problems , Subscribe here or click here to get updates via email .


I am using a remote .svc webservice and in trying to consume it in SSIS using webservice task i am getting this error "Cannot find definition for https://....svc?wsdl service description with namespace https://.... is missing parameter name:name"

join my folower please... ow ya, your link has been put in http://datakuliah.blogspot.com/2009/08/tukeran-link-do-follow-pr-0-1-2-3-4-5.html Are u Indonesian...?

Most people who order Generic Viagra online do it for some special reason or the other; either they don't have the guts to walk up the counter with a prescription to buy cheap Viagra or there are no regular pharmacies nearby. Let's keep aside the case of the illegal Viagra buyers, who buy Viagra sale without prescriptions for reasons best known to them; I have no worries about them.

Your EP address says https:// means you are using wsHttpBinding, but as the article says, wcf service task doesnt work with wsHttpBinding. Verify if this is the problem.

Great post I must say and thanks for the information.

Post a Comment