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)
' Retrieve XML variables
Dim xmlDoc As XmlDocument = CType(Variables.xmlDoc, XmlDocument)
Dim elementSuppliersBatch As 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>
xmlDoc, elementMethod, "SupplierId", Row.SupplierId.ToString())
xmlDoc, elementMethod, "CompanyName", Row.CompanyName)
Catch ex As Exception
1, "Script Component", ex.Message, "", 0, True)
The preceding code is commented, but basically it adds the following CAML statement for every record in Suppliers
<Method ID="[SupplierId]" Cmd="New">
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
), 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.