want to sync customer information with CRM solutions,
import financial data from ERP systems, export to data
warehouses, or perhaps migrate entire list-based solu-
tions 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, trans-
form, and load (ETL) operations: SQL Server Integration
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
The remainder of this article provides you with basic
tools for retrieving and storing list data using CAML
· 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 oth-
erwise need to write in SSIS.
Creating Your Web Service Proxy
To begin creating the sample application, open Visual
Studio and create a new project of type "Class Library,"
and name it something like "SharePointServices" (see
Next add a web reference to the Lists service of your
SharePoint site by following this procedure:
1. Right click on References and select "Add Web
2. In the URL field, enter http://[servername]/[site
3. Optionally, enter a reference name and click "Add
Reference" (see Figure 2, next page).
Visual Studio automatically runs WSDL.exe in the back-
ground to create a proxy class that shields you from the
Web services communication details and also provides
simple asynchronous access to the Web services meth-
At this point you can add a class with some conven-
ience functions. You're welcome to use the classes avail-
able in the downloadable solution, and that you can
also find in Listing 1. This article will discuss the helper
functions in more detail as it uses them.
Putting SharePoint to Work for You, an Internet.com Developer eBook. Copyright 2008, Jupitermedia Corp.
Putting SharePoint to Work for You
Figure 1: Class Library Project: Create a new class
library project and name it "SharePointServices."