RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Sync Your Database to SharePoint Using SSIS : Page 3

Discover how to import and export SharePoint list items using SQL Server Integration Services and the new Collaborative Application Markup Language (CAML).

Set Up SSIS for Success
Figure 4. Integration Services Project: The Integration Services Project template option appears only after you've installed SSIS.
You're now ready to create an SSIS package to migrate data. To do this, open Visual Studio and create a new project, selecting the "Integration Services Project" template that you'll find under "Business Intelligence Projects" (see Figure 4). Note that this option appears only after you install SSIS from the SQL Server 2005 install disk.

Next, create a connection to Northwind. You'll use this connection throughout the SSIS project, so if you change databases you only need to make the change in one place. Figure 5 illustrates the steps shown below:

Figure 5. New SSIS Connnection: The circled numbers show the progression of steps required to create a new SSIS database connection.
  1. Right click in the Connection Manager pane.
  2. Select "New OLE DB Connection."
  3. Click "New" in the "Configure OLE DB Connection Manager" window.
  4. Enter your server name in the Connection Manager window.
  5. Select your database.
  6. Hit OK to close both dialogs.
You'll want to create variables to hold your CAML XML commands (see Figure 6). You'll use these to build up insert, update, or delete statements and then execute them all at once. Here are the steps to create the variables:

Figure 6. Create Variables: The circled numbers show the steps to create variables to hold your CAML XML commands.
  1. Right click somewhere in the workspace and select "Variables" to ensure the variables are scoped at the Package level (incorrectly scoped variables can be a real pain to debug in SSIS, so be careful).
  2. Click the New Variable toolbar button.
  3. Enter a variable name to hold an XmlDocument (xmlDoc) and one to hold a batch of commands for each list (elementSuppliersBatch).
  4. Unfortunately there are not many predefined data types in SSIS, so you'll have to create variables of type Object and cast when you use them.
Author's Note: For simplicity this solution maintains all data in variables in memory. If you have a large amount of data (perhaps 10,000 records or more), then you would want to build the XML files on disk. However, most techniques in this article will all still apply.

You must initialize the variables in a Script Task, which allows you to run Visual Basic code. Primarily the code will create a Batch CAML command:

   <Batch OnError="Continue"></Batch> 
That command will contain all the individual insert commands. Continue is the most common value for the OnError attribute, but you may also set it to Return. Figure 7 illustrates the steps described in the following procedure:

Figure 7. Script Task: Initialize the variables in a new script task by following the numbered steps.
  1. Drag a Script Task from the Toolbox onto the Control Flow Design Surface.
  2. Double click the Script Task to open up the Script Task editor.
  3. Click the Script tab.
  4. Enter the variables from step 3, comma separated to enable this script task to update their values.
  5. Click Design Script. The Visual Studio for Applications window should open.
To complete the task, you'll need to add a reference to System.Xml, SharePointServices, and System.Web.Services by right clicking on "References" in Solution Explorer, just as you would in the regular Visual Studio environment. Then, add the following Imports statements:

          Imports System.Xml
          Imports SharePointServices
          Imports SharePointServices.NorthwindSync
Finally, paste in the following code:

   Public Sub Main()
       ' Create an XmlDocument 
       Dim xmlDoc As XmlDocument = New XmlDocument()
       ' Create two XML elements that look like 
       ' <Batch OnError="Continue"></Batch>
       Dim elementSuppliersBatch As XmlElement = _
       Dim listService As Lists = ListHelper.GetListsService()
       ListHelper.AddDeleteAllItemsBatch( _
         listService, "Suppliers", xmlDoc, elementSuppliersBatch)
       ' Save the results back to the SSIS variables
       Dts.Variables("xmlDoc").Value = xmlDoc
       Dts.Variables("elementSuppliersBatch").Value = elementSuppliersBatch
       Dts.TaskResult = Dts.Results.Success
     Catch Ex As Exception
       ' Write out an error
       Dts.Events.FireError(1, Ex.TargetSite.ToString(), Ex.Message, "", 0)
       ' Return gracefully, never return with an 
       ' exception from a script task
       Dts.TaskResult = Dts.Results.Failure
     End Try
   End Sub
Retrieving and Deleting Data with CAML
While the Main subroutine code shown in the preceding section is documented in-line, the following two lines are worth noting because they add commands to the element to delete all existing records from the Suppliers list. They also demonstrate how to retrieve list items from SharePoint.

   Dim listService As Lists = ListHelper.GetListsService()
   ListHelper.AddDeleteAllItemsBatch( _
       listService, "Suppliers", xmlDoc, elementSuppliersBatch)
The first line retrieves a Lists object, which is the object you will interact with for all list-based requests. The Lists class was generated by Visual Studio when you created the web reference. The helper method GetListsService simply instantiates an instance of the Lists class, then sets the login credentials and the URL:

   public static Lists GetListsService() {
       Lists listService = new Lists();
       listService.Credentials = CredentialCache.DefaultCredentials;
       listService.Url = "http://[server]/[site]/_vti_bin/Lists.asmx";
       return listService;
The second command is a call to the ListHelper, which adds commands to delete all existing list items. Unfortunately, there is no Truncate statement in CAML, so AddDeleteAllItemsBatch() retrieves all list items in the list and adds a "Delete" command for each. The following line retrieves all records:

      strListName, null, null, null, "1000000", null, null);
While the details of the GetListItems() function are beyond the scope of this article, if you use it in your own code you'll most frequently interact with the third argument, which takes a Where CAML clause. However, to return all records you can simply pass in null.

One trick worth mentioning is that this method retrieves about 20 items by default (or whatever is specified in your default view). Consequently, you must pass in a large number for the fifth argument (which specifies the maximum number of results to return) to prevent your results from being limited unexpectedly in production.

The GetListItems() function returns an XML node containing the query results. The AddDeleteAllItemsBatch() method then loops through these results using the following two lines of code:

   XmlNode nodeRsData = nodeListItems.ChildNodes[1];
   foreach (XmlNode nodeRow in nodeRsData.ChildNodes) {
It then retrieves the ID fields, which are the hidden primary key for all list items with this line:

Finally, the method appends Method elements (which are individual CAML commands) that look like the following:

   <Method ID="[Id]" Cmd="Delete">
      <Field Name="Id">[Id]</Field>
The Method CAML command's ID attribute provides an identifier (which incidentally does not need to be unique to the batch) that SharePoint will return to you when you commit the batch, along with a notification of success or failure.

The Cmd attribute of the Method element identifies this command as a Delete statement. The two other valid values are Insert (which you'll see later) and Update.

Finally the Field element in this context is like a WHERE Id = '[Id]' command in SQL. You'll see the Field element used in a different context shortly.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date