SharePoint Destination

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 Connection Manager

The SharePoint destination component requires a SharePoint connection in order to be able to connect to the Microsoft SharePoint server. The SharePoint Connection Manager option will show all SharePoint connection managers that have been created in the current SSIS package.

Destination List

The SharePoint List 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) - Available since v26.1

The checkbox allows the deleted records will be sent to the recycle bin, which makes the items or files recoverable.

Actions

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

• Create: Create new record(s) in SharePoint
• Update: Update existing record(s) in SharePoint
• Delete: Delete record(s) from SharePoint
• CreateFolder: Creates folder structure without writing any records or documents in SharePoint
• CheckIn: Check in record(s) in SharePoint
• CheckOut: Check out record(s) in SharePoint
• Moderate: Perform content approval in SharePoint.

Input Timezone

The Input Timezone option lets you specify how all datetime fields should be written. Available options are:

• Local System Time
• UTC

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 the incoming rows.

• Always Try to Create: default option. 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 option 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, and the overhead can be substantial if the target contains a significant number of records.
• Create Based on Folder-level Cache: this option will build the folder structure cache on an individual folder level instead of the entire list or library. Strength: performant option compared with “Create based on Full Cache” option. Weakness: this option may cause “list view threshold” error when a particular folder contains a significant number of records. If the error is ever encountered, switch to one of the above two options.
• No Folder Creation: recommended option when you are certain that your folder structure has already been populated in the target list or library, in which case it will offer the best writing performance.

Batch Size

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

Enable Multithreaded Writing (since v6.1)

Since the v6.1 release, we added support for the Multi-threading feature, which allows you to perform multi-threading when writing data to SharePoint. To configure the Multi-threading feature, you can check the Enable Multithreaded Writing option in the SharePoint Destination Component. The default number is 16.

Note: This option supports a maximum number of 100 threads when writing to SharePoint. We generally recommend 20 or lower to avoid potential server errors. However, you can adjust these settings based on your environment in order to get 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 the SharePoint list items that you create, you can specify the maximum number of attachments per list item to create. SSIS input columns will be created based on the number specified here. Each attachment column will be named KWS_Attachment_#, where # is a number. A column called KWS_Attachment_#_FileName will also be created to let you specify the file name of the file that you want to upload. If you specified a SharePoint document library, this value defaults to 1. SSIS input columns created can be used as input for upstream components.

Remove Invalid Characters

When enabled, the Remove Invalid Characters option will remove any invalid characters from the input which can avoid an XML exception when the components try to construct the SOAP request to be sent to SharePoint. Those invalid characters are usually not accepted by SharePoint even posted.

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

When enabled, the destination component will automatically enable those applicable audit fields (if any are selected) for writing before the process. As soon as all the records have been processed, the component will automatically disable them for writing - this way we keep the user behavior impact to a minimum. Note that when you depend on 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 finish the process at exactly 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 this button, the component will try to map any unmapped fields by matching their names with the 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

By clicking this button, the component will reset all your mappings in the destination component.

Columns Page

The Columns page of the SharePoint Destination Component allows you to map the columns from upstream components to SharePoint list fields for the destination entity.

On the Columns page, you would see a grid that contains three columns as shown below.

• Input Column: You can select an input column from an upstream component for the corresponding SharePoint field.
• SharePoint Field: The SharePoint list field that you are writing data to.
• Data Type: This column indicates the type of value for the current SharePoint list field.

Working with Special Fields

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

• Virtual_LocalRelativePath Field: this is a special field we added in our software that allows you to specify a local path when writing to the destination list or document library. This is mostly been used for SharePoint document libraries to facilitate writing hierarchical data. To specify the root level of the Destination List, write '/' to this field. This replaces the FileRef field in version 2.x and below.
• Attachment_x_FileName: this is the name field of the attachments for Document Libraries and Lists. The x represents the number of attachments you are trying to create. You would specify the number of attachments in the General page of the SharePoint Destination Component in the Attachment Settings (Lists only) option. For Document Library, you may only specify one.
• Attachment_x_BinaryContent: this is the image file field of the attachments for Document Libraries and Lists. The x represents the number of attachments you are trying to create. You would specify the number of attachments in the General page of the SharePoint Destination Component in the Attachment Settings (Lists only) option.
• 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.

Writing to Audit Fields

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

Depending on the chosen destination list, the available fields in the list can be different. If the destination list is a document library, only the fields will be available.

• Created
• Modified

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 ellipse button in the Text Lookup column which is available on SharePoint Destination Component's mapping page. You will be presented with the following screen:

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 happen.

There are three options available.

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

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.

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

Additional Components - Premium File Pack

In addition to the SSIS SharePoint Components listed above, the SSIS Integration Toolkit for Microsoft SharePoint software is also shipped with the support of some common functionalities of our Premium File Pack from the SSIS Productivity Pack, which includes additional SSIS components and tasks:

• Premium Excel Source Component
• Premium Excel Destination Component
• Premium File System Source Component
• Premium File System Destination Component
• Premium Flat File Source Component
• Premium Flat File Destination Component
• Premium File Transfer Task Component

Note that using those Premium File Pack functionalities to work with SharePoint document libraries or lists does not require a license of the SSIS Productivity Pack software. For additional components within SSIS Productivity Pack, a supported license would be required.

License Manager

SSIS Integration Toolkit comes with a license manager program that helps you manage and activate the product license key to be used for the toolkit. The below information is useful for development workstations and Single Server license management. For Azure-SSIS IR deployments, license management and activation will be handled through the PowerShell script, see Running SSIS Integration Toolkit on the Cloud for further details.

Without a commercial license, SSIS Integration Toolkit will operate under the Developer License which is free to use for development or evaluation purposes. Under the developer license, you can use the software within the development tool (SSDT-BI, BIDS, or Visual Studio).

The only limitation of the free developer license is the inability to run the software outside of the development tool (SSDT-BI, BIDS, or Visual Studio). If you would like to run the software outside the development tool, such as running SSIS packages on a scheduled basis or from a command line, you will need to acquire a license from us.

If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch the License Manager program, then click the "Change License Key" button, where you can request a free trial license after filling out the necessary Licensee Information.

If you have received a product license key from us after placing an order through our online shopping cart system, you can also click the "Change License Key" button and enter the product license key in order to activate the software to use the fully-featured commercial license.

The Licensee field is where you will specify the company the software is licensed to, you can include your system's machine name for future reference. For example, the Licensee can be "ABC Inc." or "XYZ Corp (SQLSVR-001)." The Contact Email would be the person we reach out to for any license-related notices such as renewal reminders.

To request a free trial license or activate a product license key that you have received, you can use the Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when the Web Service option is used. This is the option that we recommend.

Alternatively, you can choose the Email option so that the license manager will generate an email for you which you can send to us. The Email option should only be used if your system has no Internet access. It requires manual processing so please expect to wait for 24 to 48 hours before receiving a license file from us. Once you have received the license file from us through email, you can save it to a local file, which you can then install by clicking the "Install License File..." button in the License Manager.

If you have acquired a license from us, once the software has been activated, your license manager will be shown as below:

Connection Tier will display the number of distinct connections your license supports per connection type within a 24-hour period at runtime. Multiple connections to the same instance are typically treated as 1 distinct connection (exceptions may apply depending on the nature of the service). Selecting the magnify icon will launch the Runtime Connection Usage Summary window which will display counts on the number of connections made per connection type and when the 24-hour period will reset.

If you own a perpetual license from us, you should be able to see your Maintenance Expiry Date in the License Manager program. By default, your perpetual license should be instated with a one-year maintenance and upgrade included, which entitles you to use any version of the software released before your Maintenance Expiry Date. To extend your software license maintenance, log in to our portal and navigate to the license key using the License Keys menu. From there, you can renew the license or otherwise reach out to our Client Services team to request their assistance in renewing your license terms.

Note: Perpetual license is only applicable to clients who have previously acquired such a license before the deprecation of the license type. It is no longer offered for any new purchases.

If your commercial license is a subscription license, you will not see the Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period. Instead, you will see your license expiry date and a progress bar with the number of days left on your subscription.

Note: You must run the License Manager program under a local administrative account due to the privileges required to write license files to the system.

Contact Us

If you need any further assistance with the toolkit, please don't hesitate to contact us.