SharePoint provides a large number of web services and you just need to create proxy of the web service that you want to use. You can create a proxy using wsdl.exe tool in the Visual Studio command prompt. The syntax for
wsdl.exe is
wsdl.exe http://<your site name>/_vti_bin/<service name>.asmx?wsdl /o:<location>\<proxy file name>.cs /l:CS
You can choose between VB and C# and modify the command accordingly.
· Create a SSIS project and add the Data flow task. In the data flow task add Script Component as a source.
· In the script component (double click - > edit script) add the proxy (created in the first step) to the project as existing item by browsing through the proxy location. Add the reference of System.Web.Services through References
-> Add Reference.
· Add using System.Web.Services and using System.XML to the script component code.
· Now create an instance of the web service that you are using and all the methods of that web service are available for use through that instance. For accessing a particular list we can use <instance name>.GetListItems
(params…) method.
· You will have to use some XML techniques to get the required data from the list (returned by the web method) in a XML document and then filtering the required columns using Collaborative Application Markup Language (CAML) which is specifically used for SharePoint.
You can google for more information on CAML. The code for taking the data in XML is available in the Windows SharePoint Services (WSS) 3.0 SDK (again some googling will give you the code)
· Create the relevant output columns in the SSIS script component and assign the appropriate column values available in the XML node list.
· Now use the SSIS destination task to take the columns provided by the script component to the database table.
Given the variety of web services provided by SharePoint you can access the information for any level that includes document library, subsites, subsites of subsites etc. This is indeed the easiest way to get the SharePoint data in the SQL database.