Sync Your Database to SharePoint Using SSIS : Page 4

Streaming Data to CAML
After deleting all existing suppliers, you need to create a data flow task that streams the updated data from the Suppliers data table into a script component that will append Insert CAML statements to the batch. The numbered circles in Figure 8, Figure 9, Figure 10, and Figure 11 correspond to the numbered steps in the following procedure:

Figure 8. Data Flow Task: Create a new Data Flow task and connect it to the Script Task.

1. Drag a Data Flow Task onto the Control Flow design surface.
2. Connect the script task to the Data Flow Task.
3. Double click the Data Flow Task to get to the data flow screen, where SSIS will manage the stream-based flow of data from the Suppliers table to the elementSuppliersBatch variable.
4. Add an OleDb Source.
5. Double click to get to the OleDb Source Editor.
6. Select a Data Access Mode of "SQL Command."
7. Enter the SQL to retrieve some data from the Suppliers table: SELECT SupplierId, CompanyName FROM Suppliers;
8. Click OK.

Figure 9. Create an OleDb Source: The circled numbers illustrate the process to get the Suppliers data from an OleDb source.

9. Drag a Script Component onto the Data Flow design surface
10. When the dialog prompts for the script type, select "Destination," so that SSIS knows that the flow of data will go no further than this task.
11. Connect the OleDb Source to the Script Component
12. Double click the Script Component to get the Script Transformation Editor. Notice that this is similar to but not identical to the Script Task in the Control Flow.
13. Click both input columns that SSIS retrieved from the OleDb Source to indicate to SSIS that you will access them in code (in the background SSIS generates code to make these available).
14. Select the Script tab.

Figure 10. Choosing a Script Destination: The numbered steps show the process of connecting the data to a controlling script.

Figure 11. Designing the Script: In the Script Transformation Editor, select the "ReadOnlyVariables" option.

15. In the script window enter the XML Document and the Element you will write to as ReadOnlyVariables (see Figure 11). Don't use the ReadWriteVariables option, because SSIS locks the variables in that category, which prevents you from using them in other tasks. In this case, because you won't be changing the variable stack references—only their data on the heap—you can get away with using ReadWriteVariables, and make things easier down the road.
16. Click Design Script, add references to System.Xml and SharePointServices, add the lines Imports System.Xml and an Imports SharePointServices and then enter the follwing code:

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
     Try
       ' Retrieve XML variables
       Dim xmlDoc As XmlDocument = CType(Variables.xmlDoc, XmlDocument)
       Dim elementSuppliersBatch As XmlElement = _
         CType(Variables.elementSuppliersBatch, XmlElement)
   
       ' Create an "insert" CAML statement under the <Batch> element,
       ' which will look like:
       ' <Method ID="[SupplierId]" Cmd="New"></Method>
       Dim elementMethod As XmlElement = ListHelper.CreateMethod( _
         xmlDoc, elementSuppliersBatch, "New", Row.SupplierId.ToString())
   
       ' Create a two <Field> elements under the new <Method> element,
       ' which will look like:
       ' <Field Name="SupplierId">[SupplierId]</Field>
       ListHelper.AppendField( _
         xmlDoc, elementMethod, "SupplierId", Row.SupplierId.ToString())
       ListHelper.AppendField( _
         xmlDoc, elementMethod, "CompanyName", Row.CompanyName)
     Catch ex As Exception
       Me.ComponentMetaData.FireError( _
         1, "Script Component", ex.Message, "", 0, True)
     End Try
   End Sub

The preceding code is commented, but basically it adds the following CAML statement for every record in Suppliers:

   <Method ID="[SupplierId]" Cmd="New">
      <Field Name="SupplierId">[SupplierId]</Field>
      <Field Name="CompanyName">[CompanyName]</Field>
   </Method>

These should look familiar because they're similar to the Method statements you saw when deleting list items. This time, though, the Cmd attribute specifies an Insert command (New), and the Field elements specify data to insert rather than acting as a where clause.

One gotcha is that the Field element's Name attribute should refer to the internal name of the field—and that's not necessarily the same as the display name. If you are unsure of an internal field name, the easiest way to find it is to navigate to the list in SharePoint, go to the List Settings Page, click on the column, and copy and UrlDecode the value after the Field= parameter of the URL. The most common problem here is that SharePoint converts spaces to _x0020_.

Incidentally, don't forget the Try/Catch block, this is even more important in a Data Flow for debugging if something goes wrong.

Previous Page

Next Page