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. |
- Drag a Data Flow Task onto the Control Flow design surface.
- Connect the script task to the Data Flow Task.
- 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.
- Add an OleDb Source.
- Double click to get to the OleDb Source Editor.
- Select a Data Access Mode of "SQL Command."
- Enter the SQL to retrieve some data from the Suppliers table: SELECT SupplierId, CompanyName FROM Suppliers;
- Click OK.
 | |
Figure 9. Create an OleDb Source: The circled numbers illustrate the process to get the Suppliers data from an OleDb source. |
- Drag a Script Component onto the Data Flow design surface
- 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.
- Connect the OleDb Source to the Script Component
- 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.
- 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).
- 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. |
|
|
- 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 referencesonly their data on the heapyou can get away with using ReadWriteVariables, and make things easier down the road.
- 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 fieldand 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.