ustom SharePoint lists are like tables in a traditional database; in some ways they're even better. But as organizations adopt this new data store, they introduce additional complexities in integrating new list-based solutions with existing database applications. This article shows you how to solve the data mismatch problem by using the Extract Transform and Load (ETL) capabilities of Microsoft SQL Server Integration Services (SSIS) and Microsoft's new Collaborative Application Markup Language (CAML).
Why Yet Another Data Store?
If you aren't aware of the power of SharePoint custom lists yet, the topic is worth investigating. SharePoint custom lists combine the ease-of-use that has made Excel Spreadsheets and Access databases so successful among "knowledge workers" in many organizations with the benefits of traditional databases. They offer Access-like simplicity of both administration (creating and modifying lists) and data access, while overcoming one of Access' biggest challenges: decentralization.
In addition to centralizing data on a server, SharePoint lists benefit from other traditional database features such as referential integrity, indexes, and views. They also benefit from SharePoint-provided features such as native support for workflows and automatic versioning.
And if that isn't enough to convince you of the likelihood that you'll interact with them in the near future, consider how seriously Microsoft is pushing the technology by integrating SharePoint and Microsoft Office 2007. Even if you aren't using lists today, the chances are good you will be before long.
The Bane of a New Data Store: Integration
As capable as custom lists may be, they suffer from a problem common to all new technologies: the need for interoperability. At some point, list-based solutions will need to interact with other systems. Organizations may want to sync customer information with CRM solutions, import financial data from ERP systems, export to data warehouses, or perhaps migrate entire list-based solutions to consolidate stovepipe systems. Regardless of where the data is coming from or going to, Microsoft offers a powerful tool for these kinds of extract, transform, and load (ETL) operations: SQL Server Integration Services (SSIS).
SSIS to the Rescue
Even if you've used SSIS previously, interacting with list-based data in SharePoint is a little tricky. SharePoint exposes access to almost all of its inner workings through a comprehensive web services API.
While SSIS is still the best tool for the job of moving your data (far better than its obsolete cousin DTS), you'll need to learn a few new techniques. Most of these techniques are based around using a new XML-based language called Collaborative Application Markup Language (CAML).
The remainder of this article provides you with basic tools for retrieving and storing list data using CAML including:
- Calling Web Services in SSIS
- Retrieving list data with CAML
- Batch inserting data into lists using CAML
To illustrate these tasks, this article shows you how to build a SSIS application that performs a one-way sync from the Northwind database's Suppliers
table into an equivalent custom list.
Web Services in SSIS
If you're new to accessing web services in SSIS you might be inclined to use the "Web Services Task" in the toolbox. However, the task is difficult to use, and you'll have more flexibility and fewer problems if you generate the web services proxy class with Visual Studio's WSDL.exe
tool in a class library and access it in SSIS.
This approach has the additional benefit that you can write helper methods in C#, which you can reuse across all your script tasks. Furthermore, if you're more comfortable in C# code, you can minimize the amount of Visual Basic (the mandatory language of script tasks) that you would otherwise need to write in SSIS.