Using the SharePoint Source Component
The SharePoint Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from the Microsoft SharePoint server.
The SharePoint Source Component includes the following pages to configure how you want to read data from Microsoft SharePoint:
- General
- Columns
- Attachment Settings
General page
The General page of the SharePoint Source Component allows you to specify the general settings of the component.

- Connection Manager
-
The SharePoint source component requires a SharePoint connection in order to connect to the Microsoft SharePoint server. The Connection Manager option will show all SharePoint connection managers that have been created in the current SSIS package.
- Source List/Library
-
The Source List/Library option is only available after a SharePoint Connection Manager is selected. After you select a SharePoint Connection Manager, the component will retrieve a list of all available SharePoint lists and document libraries.
The following system objects are also available:
- <System>.ListMetadata: Retrieves the lists and document libraries available under the specified SharePoint site along with their metadata.
- <System>.Site: Retrieves the sites available under the specified SharePoint site.
- Show Hidden
-
The Show Hidden checkbox, when checked, allows you to see hidden SharePoint lists and document libraries.
- Source View
-
After selecting a Source List/Library, this dropdown will be populated with the selected list's available views. Selecting a view can be used to help filter for desired list items.
- Output Timezone
-
The Output Timezone option lets you specify the timezone of datetime values received from the SharePoint server. Available options are:
- Local System Time
- UTC
- Site Configured Timezone (since v26.3)
- Batch Size
-
The Batch Size option allows you to specify how many records you want to retrieve with each request.
- Retrieve First Page Only
-
The Retrieve First Page Only option can be used to limit the number of records returned when reading from SharePoint. When this option is enabled, only the first page of records will be retrieved based on the specified Batch Size.
- Include Hidden Fields
-
SharePoint uses a number of hidden fields. The Include Hidden Fields option allows you to specify whether the hidden fields are read and returned.
- Document Scope
-
The Document Scope option allows you to specify what sub-items, if any, you wish to retrieve. The available modes are:
- Root Level (Files Only): Retrieves only file items at the root level.
- Root Level (Files and Folders): Retrieves file and folder items at the root level.
- Recursive (Files Only): Retrieves file items from the Source List/Library and its subfolders recursively.
- Recursive All (Files and Folders): Retrieves file and folder items from the Source List/Library and its subfolders recursively.
- Remove ID Prefixes
-
By default, SharePoint may return ID prefixes for certain fields, such as lookup fields. For example, a lookup value may be returned in the format NNN;#Text Value. Those IDs may not have any business value for the other side of the integration, in which case you can enable the Remove ID Prefixes option to remove them from the returned values.
- Download Version History
-
You can enable the Download Version History option to retrieve the available versions for each document in a SharePoint Document Library. When this option is enabled, a secondary output of the Source Component called Version History (Document Library) will be available, which includes the available versions of each document. This feature is supported for SharePoint Document Libraries, but not Lists.
- Up to
-
This option allows you to specify the maximum number of versions to retrieve for each document. Specify 0 to download all available versions.
- CAML Query Filter
-
The CAML Query Filter text box lets you specify filter criteria to help retrieve only the records you specify. The following is a sample query for reference purposes.
<Query xmlns=''> <Where> <Eq> <FieldRef Name='MyFieldName' /> <Value Type='Text'>{MyFieldValue}</Value> </Eq> </Where> </Query>The CAML Query Filter supports using SSIS variables. Variables can be inserted using the Insert Variables option and should use a format such as @[User::VariableName] or @[System::VariableName].
Our software is shipped with a CAML Query builder tool (since v4.1) that helps you create or edit CAML queries in a visual fashion. The query builder can be launched by clicking the Launch Query Designer button. You will be prompted with the designer window as shown below.

The Query Designer allows you to configure filtering and sorting conditions. Hidden fields are not shown by default. To include them in the designer, enable the Show Hidden Fields option.
- Additional Query Options (in XML) (since v5.0)
-
The Additional Query Options text box lets you specify additional properties to influence the returned list items. This option is only applicable to a connection that uses the SOAP service endpoint. The value for this property should be in XML format, as shown below:
<Folder>Documents/SubFolder</Folder>
Since the v5.1 release, this option supports using SSIS variables. When doing so, the variable should be in a format such as @[User::VariableName] or @[System::VariableName].
- Refresh Component Button
-
Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.
Columns Page
The Columns page of the SharePoint Source Component shows the available columns from the SharePoint list or document library specified on the General page. You can select which fields should be included in the component output.
If Download Version History is enabled, you can use the output dropdown to view the columns available for each component output.
The Hide Selected Fields and Hide Unselected Fields options can be used to filter the displayed fields based on their selection status. The Filter option can be used to further narrow the displayed fields.
The columns grid displays the SharePoint field's Display Name, Field Name, and Data Type.
If you enabled downloading of attachments and documents and selected Write to Output Data Column(s) as the Download Destination, output columns will be generated and listed here based on the Column Name Prefix that you specified.

Attachment Settings Page
The Attachment Settings page of the SharePoint Source Component allows you to specify how you would like to handle lists with file attachments, or how you would like to handle reading document library files.

- Enable Downloading of Attachments and Documents
-
This checkbox enables or disables the Attachment Settings options.
- Download Destination
-
There are two options for the Download Destination dropdown:
- Save to File System
- Write to Output Data Column(s)
If you specify Save to File System, attachments or documents will be downloaded to a file system folder. If you specify Write to Output Data Column(s), the file content will be written to columns in the SSIS output buffer.
- Folder Path
-
The Folder Path option is available when Save to File System is selected as the Download Destination. It allows you to specify the folder where downloaded files should be stored.
- Prefix File Name with Column Data
-
The Prefix File Name with Column Data option is available when Save to File System is selected as the Download Destination. It allows you to select a column whose value will be added as a prefix to downloaded file names. This option is optional, but can help prevent file name conflicts.
- Column Name Prefix
-
The Column Name Prefix option is available when Write to Output Data Column(s) is selected as the Download Destination. The specified value is used as the prefix for the generated SSIS output column names. The generated column names are suffixed with a number based on the maximum number of files per list item to download, for example, MyColumn_1 and MyColumn_2.
- Max Files per List Item
-
The Max Files per List Item option allows you to specify the maximum number of files to download for each SharePoint list item. If a document library is selected as the source, this option defaults to 1.