I need to consuming webservice and the data return from the webserice is stored into the sql table using SSIS 2008. the SQL Server we are using is SQL Server 2008 R2. most of the times the data might be in GB's and we need to consider the data connection expiry issue also. Please assest me with example. its important and urgent. Thaks, Sivaji Raju

Please check this msdn blog for the same. SSIS 2005 Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

You can use the Web Service task in SSIS, available in the toolbox when you open BIDS. The task itself is very intutive. Double click the Webservice task and you'll see a window that opens up. The first thing you'll need to setup is the HTTPConnection to the Server on which the Webservice is hosted (e.g http://www.webservicex.net/CurrencyConvertor.asmx). Next, setup the WSDLFile property of the task to the location where the WSDL file for this webservice is hosted (e.g http://www.webservicex.net/CurrencyConvertor.asmx?WSDL). Once done, you can go to the "Input" tab and provide any input parameters that your webservice will need to return the data you want. Once done, go to the "Output" tab and setup a flatfile connection in which the results of the Webservice call will get written. Now you are all set to consume the webservice and store its results in a file. To load the data from this flatfile to the database table, setup a Dataflow task and map the table/columns accordingly. Note that depending on the format of the output returned by the Webservice, you might need to parse the output flatfile before loading it to a database table. Hope this helps. Cheers!! Muqadder.

Hi Muqadder, I am looking for more information on this topic. you mentioned “… depending on the format of the output returned by the Webservice, you might need to parse the output flatfile before loading it to a database table.” I am wondering what task to use in SSIS to parse the flatfile since the output flatfile returned by calling webservice is a xml file. we are investigating a solution to use web service task in SSIS for data loading from a remote system (web service provider) to our SQL server database. An example would be greatly appreciated! Thanks, Catherines

Hi Catherines, There are multiple ways of making XML data consumable via SSIS. More often that not, you can use XML Source Adapter to parse data in an xml file. However, most of the times an XML file has a whole lot of metadata inside it even though we are simply interested in the Data residing inside it (in the form of Attributes and Values). For Example, assuming that I have downloaded the data from a Webservice into an xml format (by using the technique I described above), viewing the contents of the file in any text editior would typically look like this (I used this webservice to download list of countries):- <?xml version="1.0" encoding="utf-16"?> <DataSet> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Countries"> <xs:complexType> <xs:sequence> <xs:element name="ISO2" type="xs:string" minOccurs="0" /> <xs:element name="ISO3" type="xs:string" minOccurs="0" /> <xs:element name="Country" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"> <NewDataSet> <Countries> <ISO2>AF</ISO2> <ISO3>AFG</ISO3> <Country>Afghanistan</Country> </Countries> <Countries> <ISO2>AX</ISO2> <ISO3>ALA</ISO3> <Country>Aland Islands</Country> </Countries> </NewDataSet> </diffgr:diffgram> </DataSet> And my task is to parse this XML so that I can load its relevant contents into any destination. Notice that the actual data resides between the tags <NewDataSet> and </NewDataSet>.. So, to get rid of the extra content, you'll need to remove it from the file (either manually or via a Script task using string manipulation and pattern matching). Once done, I'll be left with something that looks more meningful like below (notice the mandatory first line!!):- <?xml version="1.0" encoding="utf-16" ?> - <NewDataset> - <Countries> <ISO2>AF</ISO2> <ISO3>AFG</ISO3> <Country>Afghanistan</Country> </Countries> - <Countries> <ISO2>AX</ISO2> <ISO3>ALA</ISO3> <Country>Aland Islands</Country> </Countries> - <Countries> <ISO2>AL</ISO2> <ISO3>ALB</ISO3> <Country>Albania</Country> </Countries> </NewDataset> And when Iwe try to read the above contents via SSIS XML Source, it automatically identifies that the xml file has data consisting of 3 columns (ISO2, ISO3 and Country) and hence geneates 3 rows as output in a relational format when we execute the data flow task. HOWEVER, if you have the access to the XSLT file (structure) for your webservice (typically your Webservice developer guy should be able to help provide you one), you can process the data in XML file in alot easier way without having to go thorugh all the hassle above . The exact steps to achieve this are well documented in Matt Mason's blog entry here. Hope this helps you some way. Cheers!! Muqadder.