SSIS Integration Toolkit for Microsoft SharePoint

Installation

To install and run SSIS Integration Toolkit for SharePoint, your system must have the following components installed.

• A supported SSIS design-time or run-time, which can be one of the following:
  • SSIS 2022
  • SSIS 2019
  • SSIS 2017
  • SSIS 2016
  • SSIS 2014
  • SSIS 2012

  For SSIS run-time, the installation should be done by using the corresponding SQL Server installation media, and you must select the "Integration Services" component during the installation, as shown below:

  

  Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is preferred) in order to run our software during runtime.

  • SQL Server 2014 Service Pack 2: https://www.microsoft.com/download/details.aspx?id=53168
  • SQL Server 2014 Service Pack 1: https://www.microsoft.com/download/details.aspx?id=46694

  For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

  Target Server	Design Time
  Azure-SSIS IR	Any one of the following: SSDT for Visual Studio 2022 SSDT for Visual Studio 2019 SSDT for Visual Studio 2017
  SSIS 2022	Any one of the following: SSDT for Visual Studio 2022 SSDT for Visual Studio 2019
  SSIS 2019	Any one of the following: SSDT for Visual Studio 2019 SSDT for Visual Studio 2017 SSDT for Visual Studio 2015
  SSIS 2017	Any one of the following: SSDT for Visual Studio 2019 SSDT for Visual Studio 2017 SSDT for Visual Studio 2015
  SSIS 2016	Any one of the following: SSDT for Visual Studio 2019 SSDT for Visual Studio 2017 SSDT for Visual Studio 2015
  SSIS 2014	Any one of the following: SSDT for Visual Studio 2019 Packages created using SSDT 2019 need to have their project's TargetServerVersion setting set to "SQL Server 2014" in order to work with SSIS 2014. SSDT for Visual Studio 2017 Packages created using SSDT 2017 need to have their project's TargetServerVersion setting set to "SQL Server 2014" in order to work with SSIS 2014. SSDT for Visual Studio 2015 Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2014" in order to work with SSIS 2014. SSDT-BI for Visual Studio 2013
  SSIS 2012	Any one of the following: SSDT-BI for Visual Studio 2012 (recommended; most reliable) SSDT for Visual Studio 2019 (not recommended due to potential compatibility issues with ISV solutions) Packages created using SSDT 2019 need to have their project's TargetServerVersion setting set to "SQL Server 2012" in order to work with SSIS 2012. SSDT for Visual Studio 2017 (not recommended due to potential compatibility issues with ISV solutions) Packages created using SSDT 2017 need to have their project's TargetServerVersion setting set to "SQL Server 2012" in order to work with SSIS 2012. SSDT for Visual Studio 2015 Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2012" in order to work with SSIS 2012.

• A Windows Operating System

  Windows operating system requirement largely depends on the version of SSIS runtime or design-time selected. We don't have any additional requirements in terms of the Windows operating system as long as it satisfies the minimum requirements of SSIS runtime or design-time. The general guideline is the newer the operating system is, the better. In summary, our software should work for the following Windows operating systems.

  • For desktop systems (mostly for development workstations)
    • We generally recommend Windows 10, version 1507 or greater.
    • Windows 8.1, 8, or 7 should work fine with our software installation which supports most SSDT versions and SQL Server 2016 or below, but we highly recommend you upgrade to Windows 10 because of their support status with Microsoft.
  • For server systems (mostly for runtime deployments)
    • We generally recommend Windows Server 2016 or greater (Including Windows Server 2019 and potentially future Windows Server versions).
    • Windows Server 2012 or Windows Server 2012 R2 should work fine for SSIS versions up to 2016.
      

• A .NET Framework

• • Our software requires the installation of .NET Framework 4.5.2 or above
    • For SSIS 2016 or above, .NET Framework 4.6 (or above) is generally a prerequisite, no additional installation is required.
    • For SSIS 2014 or lower, you may turn on the .NET Framework feature or install it by downloading it from the Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at https://www.kingswaysoft.com to download the installation package.

After you have downloaded the package, you can install the software by following the installation wizard.

Working with the SSIS Toolbox

SSIS Toolbox is the first place that you will be looking for our components to be added to your ETL process during the design time.

If you are working with SSIS 2012 or later, SSIS Toolbox should be automatically available during the design time once you have an SSIS package opened provided that you have our software installed properly. If you do not see the SSIS Toolbox, click either the SSIS Toolbox menu option under the SSIS menu or the SSIS Toolbox icon in the design window's upper right corner, as shown below.

Note: When working with SSIS Toolbox, you need to make sure that you are in the right view in order to see the right components. For instance, if you are looking for a data flow component, you need to make sure that you are in the Data Flow view, not the Control Flow view. Visual Studio would show different components depending on the design view that you are currently working with.

Once the data flow components are available in the SSIS Toolbox, you can start your ETL development by dragging them from the toolbox to the Visual Studio design surface.

Using the SharePoint Connection Manager

SharePoint Connection Manager is an SSIS connection manager that can be used to establish connections with Microsoft SharePoint's Web Services. The SharePoint connection manager allows you to specify how you want to connect to your SharePoint server.

To add a SharePoint connection to your SSIS package, right-click in the Connection Manager area, and choose "New Connection..." from the context menu.

You will be prompted the "Add SSIS Connection Manager" window where you can select the "SharePoint (KingswaySoft)" item to add.

The SharePoint Connection Manager contains the following three pages which configure how you want to connect to the Microsoft SharePoint server:

• General
• Advanced Settings
• More Info

General page

The General page on the SharePoint Connection Manager allows you to specify general settings for the connection. Please note that we support two Service Endpoints as below:

• REST (SharePoint Online or On-Premises server of 2016 and above, using Odata)
• SOAP (Most SharePoint Online, On-Premises, and Federated Server Installations)

It is worth noting that REST should be the preferred option for the connection. Between the two service endpoints, there are some subtle metadata differences, so if you make a change to the connection manager, you should ideally refresh each component that uses the connection manager.

Depending on the service endpoint that you have chosen, the General page will show some different configurations as explained below.

REST Service Endpoint

Connection

Authentication Type

The Authentication Type option allows you to specify the deployment type of your SharePoint server and what authentication is used. There are two options available.

• Active Directory (On-Premises)
• OAuth (Office365, Sharepoint Online)

Service URL

Using the Service URL option, you can provide an alternative SharePoint web service URL which you might have set up in a way that's different from the default.

Timeout (secs)

The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 120 seconds.

Authentication

Use Integrated Authentication (Only for Active Directory authentication type)

This checkbox specifies whether you want to use Integration Authentication or provide separate login credentials.

User Name (Only for Active Directory authentication type)

The User Name option allows you to specify the user account that you want to use to connect to your SharePoint server. Depending on how the SharePoint connection is used, the user account needs to have proper privileges in your SharePoint system.

Password (Only for Active Directory authentication type)

The Password option allows you to specify the password for the above user account in order to log in to your SharePoint server.

Note: The Password is not included in the SharePoint connection manager's ConnectionString property by default. This is done by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be Password=myPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Domain (Only for Active Directory authentication type)

The Domain option allows you to specify the domain (e.g. Active Directory) of the above user account.

Authorization Server (URL) (Only for OAuth authentication type)

Using the Authorization Server (URL) option, you can provide an alternative region-specific base Azure AD authorization server endpoint.

OAuth Type (Only for OAuth authentication type)

This option allows you to specify the preferred type of OAuth you want to use when connecting to Sharepoint REST API. There are two options available:

• AuthorizationCode
• Certificate

AuthorizationCode Authentication Type:

Get New Token

This button completes the OAuth authentication process in order to generate a new token.

• Client Id: The Client Id option allows you to specify the unique ID which identifies the application making the request.
• Client Secret: The Client Secret option allows you to specify the client secret belonging to your app.
• Azure AD Tenant: Specify the Azure AD Tenant Id.
• Redirect Url: The Redirect Url option allows you to specify the Redirect Url to complete the authentication process.

Token Path

The path to the token file on the file system.

Token Password

The password to the token file.

Certificate Authentication Type:

Client Id

The Client Id option allows you to specify the unique ID which identifies the application making the request.

Certificate Thumbprint

The Certificate Thumbprint option is only available when the Certificate OAuth Type has been chosen, it allows you to specify the thumbprint of the certificate created for the application you have created.

Client Credentials Using Custom App

Client App Id

The Client App Id option allows you to specify the unique ID which identifies the custom application making the request.

Client Secret

The Client Secret option is available when the Custom App OAuth Type has been chosen, it allows you to specify the client secret created for the application you have created.

Tenant

Specify the tenant created for the application you have created.

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully log in to the SharePoint server.

SOAP Service Endpoint

Connection

Authentication Type

The Authentication Type option allows you to specify the deployment type of your SharePoint server and what authentication is used. There are three options available:

• Active Directory (On-Premises)
• Federation (Claim-based Authentication)
• Online Federation (Office 365, SharePoint Online)

Service URL

Using the Service URL option, you can provide an alternative SharePoint web service URL which you might have set up in a way that's different from the default.

Timeout (secs)

The Timeout (secs) option allows you to specify a timeout value in seconds for the connection. The default value is 120 seconds.

Authentication

Use Integrated Authentication

This checkbox specifies whether you want to use Integration Authentication or provide separate login credentials.

Authorization Server (URL)

Using the Authorization Server (URL) option, you can provide an alternative region-specific base Azure AD authorization server endpoint.

User Name

The User Name option allows you to specify the user account that you want to use to connect to your SharePoint server. Depending on how the SharePoint connection is used, the user account needs to have proper privileges in your SharePoint system.

Password

The Password option allows you to specify the password for the above user account in order to log in to your SharePoint server.

Note: The Password is not included in the SharePoint connection manager's ConnectionString property by default. This is done by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be Password=myPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

Domain

The Domain option allows you to specify the domain (e.g. Active Directory) of the above user account.

Home Realm Uri (Only available for Federation and Online Federation authentication types)

This option applies to the Federation and Online Federation authentication types, and it may only be required for certain federated environments. It’s important to note that the Home Realm Uri option is case-sensitive. In the case that this option is required, it should typically be in the following format.

https://adfs-server-name.mycompany.com/adfs/services/trust

Note that the Home Realm Uri option is case-sensitive.

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully log in to the SharePoint server.

Advanced Settings Page

The Advanced Settings page of SharePoint Connection Manager allows you to specify some advanced settings of the connection.

Retry on Intermittent Errors

This is an option designed to help recover from possible intermittent outages or disruption of service. It prevents the integration process from stopping due to temporary issues. Enabling this option will allow service calls to be retried upon certain types of failure. A service call may be retried up to 3 times before an exception is fired. Retries occur after 0 seconds, 15 seconds, and 60 seconds.

Warning: We have designed our retry feature carefully such that the retry should only occur when it is deemed safe to do so; however, in some occasions, such retry service calls could result in the creation of duplicate data.

Multi-Authentication Site

This option should be enabled if it is a multi-authentication SharePoint site.

Ignore Certificate Errors

This option can be used to ignore those SSL certificate errors when connecting to SharePoint Server, in case your SharePoint server is using a temporary SSL certificate.

Warning: Enabling the "Ignore Certificate Errors" option is generally NOT recommended, particularly for a production instance. Unless there is a strong reason to believe the connection is secure - such as the network communication is only happening in an internal infrastructure, this option should be unchecked for best security.

Note: When this option is enabled, it applies to all HTTP-based SSL connections in the same job process, it is not just limited to SharePoint connections.

Proxy Mode (since v5.0)

Proxy Mode option allows you to specify how you want to configure the proxy server setting. There are three options available.

• No Proxy
• Auto-detect (Using system-configured proxy)
• Manual

Proxy Server

Using the Proxy Server option, you can provide a proxy server to connect to the SharePoint server.

Port

The Port option allows you to specify the port number of the proxy server for the connection.

Username (Proxy Server Authentication)

The Username option (under Proxy Server Authentication) allows you to specify the proxy user account.

Password (Proxy Server Authentication)

The Password option (under Proxy Server Authentication) allows you to specify the proxy user's password.

Note: The Proxy Password is not included in the SharePoint connection manager's ConnectionString property by default. This is done by design for security reasons. However, you can include it in your ConnectionString if you want to parameterize your connection manager. The format would be ProxyPassword=myProxyPassword; (make sure you have a semicolon as the last character). It can be anywhere in the ConnectionString.

More Info Page

The More Info page shows some basic information about the toolkit. On this page, you can find the version information of the toolkit.

Add SharePoint Components to SSIS Toolbox

The SSIS Integration Toolkit for Microsoft SharePoint includes two data flow components. They must be shown in the SSIS toolbox before you can use (drag and drop) them in the SSIS data flow task.

If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.

Both data flow components should now appear in your SSIS Toolbox, where you can drag and drop any of them to the design surface of your SSIS data flow task.

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 three 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

The SharePoint List 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 & document libraries.

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

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time.

Retrieve First Page Only

The Retrieve First Page Only option can be used to limit the number of records to return when reading from SharePoint. When the option is enabled, the number specified in the Batch Size would be used as the limit parameter.

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): Retrieve only the file items specified at root-level.
• Root Level (Files and Folders): Retrieve the file and folder items specified at root-level.
• Recursive (Files Only): Retrieve the file items specified at Source List and its sub-folders recursively.
• Recursive All (Files and Folders): Retrieve the file and folder items specified at Source List and its sub-folders recursively.

Remove ID Prefixes

By default, SharePoint returns ID prefixes for a number of fields, such as lookup fields. The returned value of those fields are in the format of 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 option to remove them from the returned values.

Download Version

You can enable this option in order to retrieve the available versions for each document in your SharePoint Document Library. When this option is checked, a secondary output of the Source Component called Version History (Document Library) will be available which would include the available versions of your document. This feature currently only supports SharePoint Document Libraries but not Lists.

Up to

This option allows you to specify the maximum number of versions which will be retrieved for each document.

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>

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 tool can be launched by clicking the "Launch Query Designer" button, you will be prompted with the designer window as shown below.

The query designer does not show those hidden fields by default. In order to show those hidden fields, you would need to click the "Show Hidden Fields" checkbox.

Additional Query Options (in XML) (since v5.0)

The AdditionalQuery Options text box lets you specify any additional properties to influence the returned list items. The option is only applicable to a connection that uses the SOAP service endpoint. The value for this property should be in XML format like below:

<Folder>Documents/SubFolder</Folder>

Note that since the v5.1 release, this option supports using SSIS variables. When doing so, the variable should be in the 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 you all available columns from the SharePoint list that you specified on the General page. If you enabled Downloading of Attachments and Documents, and used the Output Data Column(s) option as Download Destination, output columns will be generated and listed here, based on the Column 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 whole Attachment Settings section.

Download Destination

There are two options for the Download Destination drop-down:

• Save to File System
• Write to Output Data Column(s).

If you specify the File System option, attachments from your SharePoint source list will be downloaded to a file system folder. If you specify the Output Data Column(s) option, attachments from your SharePoint source list will be inserted into a data column in the SSIS output buffer.

Folder Path

The Folder Path option is valid if you specified the File System download destination. You can specify where to store the files by selecting a folder.

Prefix File Name with Column Data

The Prefix File Name with Column Data option is valid if you specified the File System download destination. You can append some data to the prefix of the file names to be created by specifying a column to pull data from. This value is optional, but may help prevent potential file name conflicts.

Column Prefix

The Column Prefix option is valid if you specified the Output Data Column(s) download destination. You must specify a value here to be used in creating the SSIS output buffer column name. Column names to be created will be suffixed with a number depending on the maximum number of files per list item to download. (e.g. MyColumn_1, MyColumn_2)

Max Files per List Item

Because a SharePoint list item can contain multiple attachments, the Max. Files per List Item option is provided to regulate the maximum number of files to download, per list item. If you specified a document library as your source, this option will default to 1.

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.

SharePoint List

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

Action

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.

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 three more additional SSIS components and tasks:

• Premium File Pack Source Component
• Premium File Pack 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.