statement for System.XML and
SharePointServices
5. This time also add a reference to
System.Web.Services
6. Paste the code in Listing 2.
The code in Listing 2 is commented and
fairly straightforward, but there are two
important things to note. First, if you exe-
cute a CAML statement batch that has
more than about 500 statements in it you
will receive the error "Some part of your
SQL statement is nested too deeply.
Rewrite the query or break it up into small-
er queries." Fortunately, there's a
workaround. Because of this potential error
the code in Listing 2 uses a helper function
called ExecuteLargeQuery() that groups
CAML statements into smaller batches and
executes them.
If you don't think the size limitation will be
a problem after your application reaches
production then you can call
Lists.UpdateListItems() directly. The func-
tion takes two arguments: the name of the
list to execute against, and the
XmlElement that contains your CAML
statements. UpdateListItems() returns XML
that specifies the success or failure of every command in
the batch.
The second thing to note in the code is the Try-Catch
block. If UpdateListItems fails on something other than
an individual method command, the error message
isn't in the Exception.Message property as with a tradi-
tional exception. Instead you will need to catch a
SoapException and retrieve the
SoapException.Detail.InnerText property to retrieve
the error message.
Let's Run It
At this point you can run the SSIS script and, hopefully,
watch SSIS insert the 29 suppliers into your list. And, if
you run it again you should still have 29 suppliers
because SSIS deletes the first 29.
The downloadable code includes a command line appli-
cation that can create a Suppliers list (also using CAML)
in the SharePointServices solution (see Listing 3).
So now, using the techniques outlined in this article, you
should have the foundations to integrate data between
SharePoint and just about any other data source. Good
luck!
This content was adapted from Internet.com's DevX and
Developer.com Web sites. Contributors: Chi-Wei Wang,
Gustavo Velez, Lee Richardson , and Daan De
Brouckere and Raj Agarwal.
28
Putting SharePoint to Work for You, an Internet.com Developer eBook. Copyright 2008, Jupitermedia Corp.
Putting SharePoint to Work for You
[
]
Figure 13: Successful Migration: When you run the SSIS script,
you'll see the 29 suppliers appear in the list.