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 2017
    • SSIS 2016
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.

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

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

    • When targeting SSIS 2017 or 2016, you would use SSDT for Visual Studio 2015 available for download at https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version. 
  • .NET Framework 3.5
    • For any version before v4.1, .NET Framework 3.5 is required. For v4.1 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
      • If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
      • If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
      • If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
  • Windows Installer 4.5
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. On the download page you will find two download links. One for x86 systems and one for x64 systems. Make sure to choose and download the correct package for your system. 

download options

Once you click the download link, save the zip file which contains the .msi file. Double click the .msi file to start the setup. A security warning may appear. You can safely accept the warning and continue the setup.

The SSIS Integration for Microsoft SharePoint Setup Wizard will appear. Click ‘Next’ to continue.

SharePoint Integration installation

Select the checkbox to agree to the terms of the End-User License Agreement. Click 'Next' to continue.

SharePoint Integration installation

Next, select one of the three setup types that best suits your needs.

SharePoint Integration installation

Click ‘Install’ to begin the installation.

SharePoint Integration installation

To complete the installation, select 'Finish'.

SharePoint Integration installation

Setup a SharePoint Connection 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 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" item to add.

new connection

Add SharePoint Connection

The SharePoint Connection Manager contains the following three pages which configures 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.

SharePoint Connection Manager

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-Premise)
  • 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 the way that's different from the default.

Home Realm Uri (since v4.3)

Specify the URI of the cross realm STS service endpoint. It should typically be a URL starting with https. This option only applies to the Federation authentication type, and it may only be required for certain federated environment. 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.
Use Integrated Authentication

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

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

Timeout (secs)

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

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully login 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. 

SharePoint Connection Manager

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 (since v4.2)

This option can be used to ignore those SSL certificate errors when connecting to SharePoint server.

Warning: Enabling "Ignore Certificate Errors" option is generally NOT recommended, particularly for 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 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 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. In this page, you can find the version information of the toolkit.

SharePoint Connection Manager

Add SharePoint Components to SSIS Toolbox

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

  • If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
  • 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.

    SSIS Toolbox

Add SharePoint Data Flow Components to SSIS Toolbox (SQL Server 2008 R2 or earlier)

In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

choose items

You will be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select SharePoint Destination and SharePoint Source components from the list. 

Add SharePoint SSIS Components

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. 

Use of 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
  • Attachment Settings
  • Columns

General page

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

SharePoint Source Editor

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. For performance reasons, this parameter should not be too small.

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.

Remove ID Prefixes(since v4.1)

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.

Retrieve First Page Only(since v4.5)

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.

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

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

SharePoint CAML Query Designer

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. Please note that the value provided to this property should be in XML format like below:

<Folder>Documents/SubFolder</Folder>
Note that since 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. 

SharePoint Source Editor

Attachment Settings

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.

SharePoint Source Editor

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 it is possible for a SharePoint list item to 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.

Use of 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, or delete 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

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 (since v4.5) - Check in record(s) in SharePoint
  • CheckOut (since v4.5) - Check out record(s) in SharePoint
  • Moderate (since v6.0) - Perform content approval in SharePoint.
Batch Size

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

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 in as input for upstream components.

Remove Invalid Characters (since v5.1)

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

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. 

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

SharePoint Destination Editor

  • 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 which allows you to specify 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 root level of 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, 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

Since v2.0, we added support for Text Lookup feature, which allows you to perform lookup based on the text values of the target object. To configure Text Lookup feature, click the ellipse button in Text Lookup column which is available in 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 Use Text Lookup option. When you have checked 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 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, 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 text lookup feature, particularly when the number of records in 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 hundreds of thousands of records in lookup entity, and you are only processing a few dozens of 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.

Error Handling page

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

SharePoint Destination Editor

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 represent rows that were successfully written, and the red 'Error Output' connection represents rows that were erroneous. 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 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:

Note that using those Premium File Pack functionalities to work with SharePoint document libraries or lists requires no extra license of the SSIS Productivity Pack software.

License Manager

The SSIS Integration Toolkit comes with a license manager program which helps you manage and activate the product license key to be used for the toolkit.

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, BIDS, or Visual Studio). 

KingswaySoft License Manager

The only limitation with the free developer license is that you cannot run the software outside of the development tool (SSDT, 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 License Manager program, then click "Change License Key" button, where you can request for a free trial license after filling out the necessary Licensee Information.

KingswaySoft License Manager

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

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, which is the option that we recommend.

Alternatively, you can choose the Email option. The license manager will generate an email which you can send to us. The Email option should only be used if your system has no Internet access, and requires our team to manually process your order, so please expect to wait 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 "Install License File..." button in License Manager.

If you have acquired a perpetual license from us, after you have activated the software as shown above, your license manager will look something like below.

KingswaySoft License Manager

Since v2.0, you can see your Support Expiry Date or Maintenance Expiry Date in the License Manager program if you are using a perpetual license. By default, your perpetual license comes with one-year maintenance and upgrade, which entitles you to use any version of the software released before your Support Expiry Date or Maintenance Expiry Date. You can extend your support expiry date by entering a new maintenance license key that you have acquired from us.

KingswaySoft License Manager

If your commercial license is a subscription license, you will not see the Support Expiry Date or Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period.

NOTE: Starting from v2.0, you need to run License Manager program under a local administrative account due to the privileges required to write license file to the system.

Contact Us

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