Executing the Batch
The last step is to write the code to execute the Batch of delete and insert commands in SharePoint. Figure 12
shows the completed code flow:
|Figure 12. Complete Code Flow: Here's the completed code flow in the designer.|
- Add a second script task following the instructions given previously.
- Connect the Data Flow Task to it and then open the script task.
- Under the "ReadOnlyVariables" enter elementSuppliersBatch.
- Edit the code, and add a reference to and Imports statement for System.XML and SharePointServices
- This time also add a reference to System.Web.Services
- 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 execute 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 smaller queries." I've documented this problem in the blog post, "CAML: Nested Too Deep
," but 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 function 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.
|Figure 13. Successful Migration: When you run the SSIS script, you'll see the 29 suppliers appear in the list.|
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 traditional 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 application 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!