With SharePoint’s growing popularity in the business world, it’s become an important repository of data. However, this data is often isolated from the rest of an organization’s data system. But by using the native web-service calls in Windows SharePoint Services (WSS), you can access SharePoint list data and use SQL Server Integration Services (SSIS) to transform the resulting dataset from an XML source to a relational database table.
This import routine can be automated via SQL Server Agent and scheduled to run at regular intervals to keep the SharePoint list and relational database in sync. That way, SharePoint list data need not be a data island in the midst of an otherwise integrated data environment.
SharePoint Data Storage and Access
Before we walk through the step-by-step process to integrate SharePoint data with SQL Server, let’s look at list-data storage and retrieval in SharePoint. SharePoint pages are ASP.NET applications whose content resides in a back-end SQL Server database called the content database. Other SharePoint–related data, such as list data and document library attachments, is also stored in the content database.
Although server administrators might be able to access the SharePoint content database and the underlying tables, Microsoft doesn’t recommend or support accessing SharePoint data this way. However, Microsoft does support other ways of accessing this data: by using the SharePoint Service object model or by using SharePoint Services web services. The object-model approach is more development intensive and requires familiarity with a myriad of object-model namespaces, hierarchies, classes, and enumerations. The web-services approach provides a more straightforward alternative. Although it still requires some coding, it’s fairly lightweight in comparison and is the approach we’ll use.
SharePoint Web Services and SSIS
SharePoint Services web services include methods for accessing data on a website, such as for working with lists or site data, as well as methods for customizing content such as meetings, imaging, document workspaces, or search. The web services provide their functionality through the /_vti_bin virtual directory, which maps to the %PROGRAMFILES%\Common Files\Microsoft Shared\web server extensions\12\ ISAPI physical directory in the file system.
The Lists web service for example, which is what you would call to access list data, can be accessed from the path servername/sitename/_vti_bin/lists.asmx. The web-service method calls often require input parameters, typically in plain text or CAML format. CAML is an XML–based markup language used exclusively with SharePoint technologies. As with any web-service method call, the result set is in XML format.
SSIS provides a platform to build a data integration application. Although it uses a Visual Studio (VS) 2005 interface to build an application, it features drag-and-drop usability for rapid development. It also comes standard with a wide array of tools known as Control Flow tasks to connect to various types of data sources and destinations.
Two such Control Flow tasks that we can use for our solution here are the Web Service task and the Script task. Both these objects can connect to a web service to retrieve data. To illustrate this integration, I’ve created a scenario that uses a WSS site containing a list with some pre-populated data.
I created the site by using the Absence Request and Vacation Schedule Management application template, which is available for download from Microsoft (technet.microsoft.com/ en-us/windowsserver/sharepoint/bb407286.aspx). I populated the list, named Absences, with some sample data.
Setting Up a Project and Configuring a Script Task
To create a new SSIS project, open SQL Server Business Intelligence Development Studio. BIDS is a subset of VS 2005 and is included in the default installation of SQL Server 2005 Standard Edition or higher. Create a new project of type Business Intelligence Projects using the Integration Services Project template.
In the project window, expand the toolbox on the left side. This displays a long list of Control Flow items, pre-packaged SSIS items that can be combined to form a data-integration application and dragged and dropped into the project.
The Web Service task is one such item on this list and seems to be the obvious choice. But it can’t support web services that accept complex input parameters such as CAML queries, and to query a SharePoint list, we need to be able to provide a CAML fragment as an input parameter. For that matter, it can’t be used to call a majority of SharePoint web services. (It is, however, compatible with some of the simpler web-service methods such as Webs.)
Instead, we’ll use the Script task, a flexible and extensible SSIS task item that lets you write .NET code and provides a bridge between SSIS objects and external applications. In the scenario here, we want the Script task to call up the SharePoint Lists web service, read certain columns of data from a specific list in a SharePoint site, and store the resulting data in a SQL Server database destination.