ASP.NET Custom role provider

Easy guide to write your custom role provider

Merge splitted .mkv files

Learn how to merge splitted .mkv files (.mkv001,.mkv002...)

How to Zoom in and Zoom out your sql execution plan ?

Tool to tune your sql query

send .exe files with gmail

How to send files having blocked extension with Gmail ?

Multiple home pages in Internet explorer

How to set multiple home pages in internet explorer ?


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 .