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 .

<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_ReportService"
closeTimeout="00:05:00"
openTimeout="00:05:00"
receiveTimeout="00:05:00"
sendTimeout="00:05:00"
maxBufferPoolSize="524288"
maxReceivedMessageSize="2147483647">
<readerQuotas
maxDepth="2147483647"
maxStringContentLength="2147483647"
maxArrayLength="2147483647"
maxBytesPerRead="2147483647"
maxNameTableCharCount="2147483647" />
<security mode="None" />
</binding>
</basicHttpBinding>
</bindings>

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 .

<bindings>
<basicHttpBinding >
<binding name="BasicHttpBinding_MPCService"
closeTimeout="00:01:00"
openTimeout="00:01:00"
receiveTimeout="00:10:00"
sendTimeout="00:01:00"
allowCookies="false"
bypassProxyOnLocal="false"
hostNameComparisonMode="StrongWildcard"
maxBufferPoolSize="2147483647"
maxReceivedMessageSize="2147483647"
messageEncoding="Text"
textEncoding="utf-8"
useDefaultWebProxy="true">
<readerQuotas
maxDepth="2147483647"
maxStringContentLength="2147483647"
maxArrayLength="2147483647"
maxBytesPerRead="2147483647"
maxNameTableCharCount="2147483647" />
<security mode="None">
<transport clientCredentialType="None" proxyCredentialType="None" realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
</bindings>

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 .