Using the SharePoint Destination Component

The SharePoint Destination Component is an SSIS data flow pipeline component that can be used to write data to a destination Microsoft SharePoint server. You may Create, Update, Delete, CreateFolder, CheckIn, CheckOut, or Moderate SharePoint list items using the SharePoint Destination Component.

The SharePoint Destination Component includes the following three pages to configure how you want to write data to Microsoft SharePoint server.

  • General
  • Columns
  • Error Handling

The General page is used to specify general settings for the SharePoint destination component. The Columns page allows you to map the columns from upstream components to SharePoint list fields in the destination entity. The Error Handling page allows you to specify how errors should be handled when they occur.

General Page

The General page of the SharePoint Destination Component allows you to specify general settings for the component.

SharePoint Destination Editor

Connection Manager

The SharePoint destination 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.

Destination List/Library

The Destination List/Library option allows you to specify which SharePoint list or document library to write data to.

Show Hidden

The Show Hidden checkbox, when checked, allows you to see hidden SharePoint lists and document libraries.

Use Recycle Bin (Soft Delete) (since v26.1)

The Use Recycle Bin (Soft Delete) option is available when using the Delete action. When enabled, deleted items or files are sent to the SharePoint Recycle Bin so they can be recovered if needed.

Action

The Action option allows you to specify how data should be written to the Microsoft SharePoint server. There are currently seven supported action types available.

  • Create: Creates new record(s) in SharePoint.
  • Update: Updates existing record(s) in SharePoint.
  • Delete: Deletes record(s) from SharePoint.
  • CreateFolder: Creates a folder structure without writing any records or documents in SharePoint.
  • CheckIn: Checks in record(s) in SharePoint.
  • CheckOut: Checks out record(s) in SharePoint.
  • Moderate: Performs content approval in SharePoint.
Input Timezone

The Input Timezone option allows you to specify the timezone of datetime values received from upstream components before writing to SharePoint. Available options are:

  • Local System Time
  • UTC
  • Site Configured Timezone (since v26.3)
Folder Creation Mode (since v21.1)

The Folder Creation Mode option allows you to choose the mode for creating folders on-the-fly while processing incoming rows.

  • Always Try to Create: The default option. The component will attempt to create the folder structure for each record received using a try-and-error approach and a local cache.
  • Create Based on Full Cache: Recommended if the folder structure has not been created in the target list or library. This option will populate the full cache for the folder structure in the target list or library. The overhead can be substantial if the target contains a significant number of records.
  • Create Based on Folder-level Cache: This option builds the folder structure cache on an individual folder level instead of for the entire list or library. It can provide better performance than the Full Cache option, but may cause a list view threshold error when a particular folder contains a significant number of records. If the error is encountered, switch to one of the other folder creation modes.
  • No Folder Creation: Recommended when you are certain that the folder structure has already been populated in the target list or library. This option provides the best writing performance when no folders need to be created.
Batch Size

The Batch Size option allows you to specify how many records you want to submit to the SharePoint service with each request.

Enable Multithreaded Writing (since v6.1)

The Enable Multithreaded Writing option allows you to use multiple threads when writing data to SharePoint. When enabled, the Use up to option allows you to specify the maximum number of threads to use. The default value is 16.

Note: This option supports a maximum of 100 threads when writing to SharePoint. We generally recommend 20 or fewer to avoid potential server errors. However, you can adjust this setting based on your environment to achieve the best performance.

Note: When this option is enabled, the record order may not be maintained from the upstream pipeline component.

No. of Attachment Columns

If you wish to upload attachments to SharePoint list items that you create, the No. of Attachment Columns option allows you to specify the maximum number of attachments per list item. SSIS input columns are generated based on the number specified here. Each attachment uses an Attachment_#_BinaryContent column for the file content and an Attachment_#_FileName column for the file name, where # represents the attachment number. If a SharePoint document library is selected, this value defaults to 1.

Remove Invalid Characters

When enabled, the Remove Invalid Characters option removes invalid characters from the input to help prevent XML exceptions when the component constructs SOAP requests to be sent to SharePoint. Such invalid characters are generally not accepted by SharePoint.

Automatically Enable and Disable Audit Fields for Writing (as needed) (since v7.1)

When enabled, the destination component will automatically enable applicable audit fields that have been selected for writing before processing begins. Once all records have been processed, the component will automatically disable those fields for writing again to minimize the impact on normal SharePoint user behavior.

Note: When using this feature, you should not use multiple SharePoint Destination Components to write to the same SharePoint list or library simultaneously with this option enabled, as there is no way to guarantee that all components will finish processing at the same time.

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.

Map Unmapped Fields Button

By clicking the Map Unmapped Fields button, the component will try to map unmapped fields by matching their names with input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped fields.

Clear All Mappings Button

Clicking the Clear All Mappings button clears all field mappings in the destination component.

Columns Page

The Columns page of the SharePoint Destination Component allows you to map columns from upstream components to SharePoint fields in the selected destination list or document library.

The Hide Mapped Fields and Hide Unmapped Fields options can be used to filter the displayed fields based on their mapping status. The Filter option can be used to further narrow the displayed fields.

SharePoint Destination Editor

The columns grid contains the following columns:

  • Input Column: Allows you to select an input column from an upstream component for the corresponding SharePoint field.
  • SharePoint Field: The SharePoint field that you are writing data to.
  • Text Lookup: Allows you to configure the Text Lookup feature for supported lookup fields.
  • DataType: Indicates the data type of the SharePoint field.
  • Unmap: Clears the mapping for the corresponding SharePoint field.

Working with Special Fields

There are some special fields in our software that require special attention when writing to them.

  • Virtual_ParentFolderPath Field: This is a special field that allows you to specify the parent folder path when writing to the destination list or document library. This is mostly used for SharePoint document libraries to facilitate writing hierarchical data. To specify the root level of the Destination List/Library, write "/" to this field.
  • Attachment_x_FileName: This is the file name field for attachments in Document Libraries and Lists. The x represents the number of the attachment you are trying to create. You can specify the number of attachments on the General page of the SharePoint Destination Component. For a Document Library, only one attachment can be specified.
  • Attachment_x_BinaryContent: This is the binary content field for attachments in Document Libraries and Lists. The x represents the number of the attachment you are trying to create.
  • Taxonomy Field: This managed metadata field is a special lookup field in SharePoint. When writing to this field, you can pass in the text value of the taxonomy item, and our software will perform an implicit lookup. Multi-value taxonomy fields also support JSON-format input values.

Writing to Audit Fields

It is possible to write data to audit fields in SharePoint. Since our v5.1 release, we have added support for enabling audit fields for writing. To do this, click the Add Audit Fields button on the destination component's Columns page, which will bring you to the following screen.

SharePoint Destination Component - Add Audit Fields

Depending on the selected destination list or document library, the available audit fields may be different. For a document library, the following fields are available:

  • Created
  • Modified

Note: We recommend using the Automatically Enable and Disable Audit Fields for Writing (as needed) option on the General page when applicable. This allows the component to enable the required audit fields before processing and disable them again after all records have been processed, which helps minimize the impact on normal SharePoint user behavior.

Working with Text Lookup Feature

The Text Lookup Feature allows you to perform lookup based on the text values of the target object. To configure the Text Lookup feature, click the ellipsis button in the Text Lookup column which is available on SharePoint Destination Component's mapping page. You will be presented with the following screen:

Text Lookup Screenshot

You can configure the Text Lookup setting by toggling the Use Text Lookup option. When you have checked the Use Text Lookup option, you will see a list of the target objects for the involved lookup field.

By using the Text Lookup option, you can specifically choose which text field should be used for the lookup purpose. You will be presented with a list of the target object for the lookup field. In this list, you will be able to see the following options.

  • Target text/integer field: You can choose a text or integer field that will be used for the lookup purpose.
  • Optional default value (if no match): When specified, the component will use this default value to perform the lookup should the input value lookup fail.

Note: In order for the Text Lookup feature to work efficiently, we populate an internal cache when a lookup is first requested. The cache is created by querying data from the target object, which would consume some SharePoint API calls.

In addition to the above settings, the Text Lookup feature also offers the following additional advanced options.

  • Ignore case: When chosen, the Text Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
  • Report error on duplicates: When chosen, the Text Lookup feature will report an error when a duplicate is encountered at the time the lookup cache is populated.
  • Cache Strategy: You can choose from one of the two options:
    • Full Cache: When chosen, the component will populate a full cache of all records from the lookup object before the lookup. This is generally the preferred option for the text lookup feature, particularly when the number of records in the lookup entity is small. This option is the default mode.
    • Partial Cache: When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in lookup objects is significantly large. For instance, if you have more than a few hundred thousand records in the lookup entity, and you are only processing a few dozen records for your primary object, Partial Cache mode would provide better performance in this kind of case.

Note: If duplicates are found when populating the lookup cache, the first one will be chosen.

Note: Since the v7.0 release, we added support for the multi-value lookup column in the SharePoint Destination Component, which allows you to write to the SharePoint lookup column with the "Allow multiple values" option enabled. To write to this type of field, just separate the lookup values with a semi-colon ";". For example, value1;value2;value3.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they occur.

SharePoint Destination Editor

There are three options available.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

The Additional Columns for Default Output section allows you to make the following additional columns available in the destination component's default output:

  • SharePoint_ID
  • SharePoint_DocUrl

The Additional Columns for Error Output section allows you to make the following additional columns available in the destination component's error output:

  • SPRequestGuid
  • SPServerResponseTimestamp

When the Redirect rows to error output option is selected, rows that failed to write to SharePoint will be redirected to the 'Error Output' of the Destination Component. As indicated in the screenshot below, the green output connection represents rows that were successfully written, and the red 'Error Output' connection represents erroneous rows. The 'SPErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by SharePoint or the component itself.

error output

Note: Use extra caution when selecting the Ignore error option, since the component will remain silent for any errors that have occurred.

Additional Components - Premium File Pack

The SSIS Integration Toolkit also includes Premium File Pack components and tasks that can be used when working with SharePoint document libraries or lists: