COZYROC SharePoint components, part of COZYROC SSIS+ suite since 2010, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from Microsoft SharePoint lists. Uploading and downloading attachments that are associated with list items is also supported. We will preserve the integrity of your data by always using the recommended Microsoft web services API. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for SharePoint data. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components. In addition, COZYROC also provides short and informative demonstration videos, providing step-by-step instructions on how to get started.
The COZYROC SSIS+ library also includes the powerful and elegant Data Flow Task Plus, not found in any other competitive product on the market. Data Flow Task Plus allows creation of dynamic data flows at runtime and can be used with COZYROC's SharePoint adapters. You can create processes where the addition or deletion of SharePoint fields doesn't necessitate modification of existing SSIS packages. This powerful feature requires no programming skills to use and greatly simplifies your maintenance efforts.
The SharePoint integration package consists of four main parts:
- SharePoint Connection Manager
- Stores the credentials and functionality for connecting to an on-premise SharePoint server or online SharePoint service (Office 365)
- Well documented API for use by user-defined scripts.
- SharePoint Source component
- Retrieves data from a SharePoint list.
- A CAML statement may be used to filter the list items in the Input List.
- The source can be dynamically modified at runtime using an SSIS expression.
- SharePoint Destination component
- Inserts or updates data in a SharePoint list.
- Supports three SharePoint list actions: Create, Update or Delete.
- Provides information about rows with errors as well as the GUID assigned for new records created.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional SharePoint integration scenarios.
- Send files
- Receive Files
- Create remote directory
- Remove remote directory
- Delete remote files
- Rename remote file
- Get remote files list
- Support Office 365, Premise deployments.
- Support SharePoint 2019, 2016, 2013, 2010, 2007.
Folders and lists on the SharePoint server/service may be managed using the COZYROC File Transfer Task:
Technical Specifications
SharePoint Source
In this section we will show you how to set up a SharePoint Source component.
- Before you begin, configure a SharePoint connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the SharePoint Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- Make a selection from the InputList drop-down list. In this example, we have selected “Shared Documents”.
- Next, make a selection from the InputView drop-down list. In this example, we have selected “All Documents”.
- Click OK to close the component editor.
Congratulations! You have successfully configured the SharePoint Source component.
SharePoint Destination
In this section we will show you how to set up a SharePoint Destination component. Note that the File Transfer Task should be used if you wish to upload files to SharePoint.
- Before you begin, drag the Data Flow Task onto the Control Flow canvas.
- Double-click on the Data Flow Task to go to the Data Flow canvas.
- Configure a SharePoint connection manager.
- Configure a source component that will provide the data to be written to SharePoint. If your input contains a Date/Time column, ensure that the datatype is set to DT_DBTIMESTAMP.
- In the SSIS Toolbox, locate the SharePoint Destination component and drag it onto the Data Flow canvas.
- Connect the blue arrow from the source component to the SharePoint Destination component.
- Double-click on the SharePoint Destination component.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab in the component editor.
- Choose the desired Action (Create, Update, or Delete). In this example, we chose the Create action.
- Choose the DestinationList you will be working with. We chose the Calendar DestinationList in this example.
- Click OK to close the component editor.
Congratulations! You have successfully configured the SharePoint Destination component.
SharePoint Connection
In this section we will show you how to set up a SharePoint Connection Manager to access your SharePoint service.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down the list to find the SHAREPOINT connection manager and select it. Click the Add button.
- Once the connection manager dialog opens, select the appropriate Deployment option (Premise or Online). In this example, we selected Online.
- Enter the URL for your SharePoint Server.
- Enter your User name, which is the email address associated with your SharePoint account.
- Enter your Password.
- Press Test Connection.
- When you see Test Connection succeeded, click OK.
- Click OK to close the connection manager dialog.
Congratulations! You have successfully configured a SharePoint connection manager.
Multi-Factor Authentication (MFA) enabled systems do require the input of random value that only a human can provide for a successful connection. To make it possible to connect to MFA-enabled systems, you can manually enter a long-lived token/cookie to connect to the system. Check the steps below for instructions on what is required to make it work.
- Open Chrome to the SharePoint authentication page and enter your credentials. Enter the required MFA code when asked. After successful authentication, the main SharePoint page will open.
To get the required token/cookie, you have to open a connection to SharePoint Online using a web browser. For this tutorial, we will use the Google Chrome browser and specific functionality found in Chrome to get the cookie. You can extract the required cookie with other tools like Fiddler.
- Open Chrome DevTools by right-click and select Inspect (Ctrl-Shift-I).
- Inspect the "Application" tab and under "Storage" > "Cookies" sections find your Sharepoint application.
- From the list of cookies, you need to extract two cookies -
FedAuthandrtFa. Copy and paste their values into Notepad.
- The two cookies have to be combined into one element, separated with a newline character. This is the needed authentication token. The result value should look like:
FedAuth=77u/PD94bWw...\nrtFa=+BNjXfKOuFc2az99...
Notice above the special combination above used as a newline separator.
- Open an SSIS package with a COZYROC SharePoint connection manager in it. Right-click on it and select Properties. Find a property named Token and press the '...' to open the entry dialog.
- Enter the token you have assembled in the prior step and press the OK button.
Done. Your connection will now use the specified token to authenticate to the SharePoint service. You can also configure an expression to modify this property with an external configuration file or a table.
Overview
SharePoint Connection Manager is SSIS Connection Manager for establishing SharePoint connections. Use the API to build components based on SharePoint Connection Manager.
Demonstration
Quick Start
In this section we will show you how to set up a SharePoint Connection Manager to access your SharePoint service.
- Right-click on the Connection Manager canvas.
- Select New Connection.
- When the list of connection managers appears, expand the Type column, if necessary, so you can see the complete names of the connection managers.
- Scroll down the list to find the SHAREPOINT connection manager and select it. Click the Add button.
- Once the connection manager dialog opens, select the appropriate Deployment option (Premise or Online). In this example, we selected Online.
- Enter the URL for your SharePoint Server.
- Enter your User name, which is the email address associated with your SharePoint account.
- Enter your Password.
- Press Test Connection.
- When you see Test Connection succeeded, click OK.
- Click OK to close the connection manager dialog.
Congratulations! You have successfully configured a SharePoint connection manager.
Multi-Factor Authentication (MFA) enabled systems do require the input of random value that only a human can provide for a successful connection. To make it possible to connect to MFA-enabled systems, you can manually enter a long-lived token/cookie to connect to the system. Check the steps below for instructions on what is required to make it work.
- Open Chrome to the SharePoint authentication page and enter your credentials. Enter the required MFA code when asked. After successful authentication, the main SharePoint page will open.
To get the required token/cookie, you have to open a connection to SharePoint Online using a web browser. For this tutorial, we will use the Google Chrome browser and specific functionality found in Chrome to get the cookie. You can extract the required cookie with other tools like Fiddler.
- Open Chrome DevTools by right-click and select Inspect (Ctrl-Shift-I).
- Inspect the "Application" tab and under "Storage" > "Cookies" sections find your Sharepoint application.
- From the list of cookies, you need to extract two cookies -
FedAuthandrtFa. Copy and paste their values into Notepad.
- The two cookies have to be combined into one element, separated with a newline character. This is the needed authentication token. The result value should look like:
FedAuth=77u/PD94bWw...\nrtFa=+BNjXfKOuFc2az99...
Notice above the special combination above used as a newline separator.
- Open an SSIS package with a COZYROC SharePoint connection manager in it. Right-click on it and select Properties. Find a property named Token and press the '...' to open the entry dialog.
- Enter the token you have assembled in the prior step and press the OK button.
Done. Your connection will now use the specified token to authenticate to the SharePoint service. You can also configure an expression to modify this property with an external configuration file or a table.
Parameters
Server
Use the Server page of the SharePoint Connection Manager dialog to specify parameters for connecting to SharePoint service.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify deployment type. This parameter has the options listed in the following table.
Value Description Premise Connect to on-premise SharePoint server. Online Connect to SharePoint Online (Office 365) service. Specify the name or IP address of the SharePoint server.
Specify user name to access SharePoint. If not specified, the connection will be established using Windows authentication.
Note: Windows authentication cannot be used with SharePoint Online.Specify password to access SharePoint.
Specify the number of seconds before timing out session connect. The default value of this parameter is 100 seconds.
Specify if all clients should use the same connection. This parameter has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Proxy
Use the Proxy page of the SharePoint Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
- Where can I find the documentation for the SharePoint Connection?
- When I try to use SharePoint 2010 server with mixed-mode authentication I get error: "403 forbidden"
- After I upgraded, why are FedAuth and rtFa options missing from the Sharepoint Connection?
- Error Message: 'The remote server returned an error (429)' when using Sharepoint Online.
- Error Message: The operation has timed out.
What's New
- New: Experimental feature to explicitly specify authentication token when using MFA-enabled service. For further information please review 'Establish connection to MFA-enabled service' Quick Start.
- New: Support for on-premises servers that use ADFS authentication.
- Fixed: Failed to delete file with ampersand (&) special symbol in the name when using File Transfer Task (Thank you, Nandu).
- Fixed: File Transfer Task was not working properly because of different file name case.
- Fixed: File Transfer Task 'Create Remote Directory', 'Remove Remote Files', 'Rename Remote File' actions did not work properly when used with SharePoint sub-site.
- Fixed: File Transfer Task did not report failure errors.
- Fixed: Failed to connect to SharePoint Online when third-party Ping token service was used (Thank you, Pradeep).
- Fixed: File Transfer Task was not working properly when used with SharePoint sub-site (Thank you, Michael).
- New: Support for sites migrated from BPOS (Thank you, Angel).
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Support for Office 365 deployed systems with Federated (delegated) security (Thank you, Phil).
- New: Introduced connection.
Related documentation
Overview
SharePoint Source Component is an SSIS Data Flow Component for consuming data from the SharePoint Service. The configuration parameters are provided in the standard Data Flow Component dialogs.
Demonstration
Quick Start
In this section we will show you how to set up a SharePoint Source component.
- Before you begin, configure a SharePoint connection manager.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the SharePoint Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab.
- Make a selection from the InputList drop-down list. In this example, we have selected “Shared Documents”.
- Next, make a selection from the InputView drop-down list. In this example, we have selected “All Documents”.
- Click OK to close the component editor.
Congratulations! You have successfully configured the SharePoint Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing SharePoint connection manager.
Related Topics: SharePoint Connection Manager
Specify the number of rows to be retrieved as a batch. The maximum is 5000.
Select a SharePoint list.
Specify a CAML query statement. This parameter is optional.
Select the SharePoint list view to use for input. This parameter is optional.
Indicate that the folders should be included in the result list. The default is false.
Indicate that sub-folder items should be included in the result list. The default is false.
Indicate that hidden list fields should be included. The default is false.
Knowledge Base
- Where can I find the documentation for the SharePoint Source?
- Missing fields when using SharePoint Source Component
- How to modify the source dynamically at runtime
- Error Message: 'The remote server returned an error (429)' when using Sharepoint Online.
- If our SharePoint service is MFA-enabled, does this mean we can't use the SharePoint components?
What's New
- New: An enhanced configuration dialog with improved output columns page.
- Fixed: Removed alphabetical columns sort.
- Fixed: Multiple fixes in CAML statement design dialog (Thank you, Luigi).
- Fixed: Component did not work in dynamic data flow (Thank you, Masthan).
- New: Component now shows user-friendly names for columns containing spaces (Thank you, Michael).
- New: Component now includes column with list item's attachments (Thank you, Anthony).
- New: A new parameter IncludeHidden to include list's hidden fields.
- New: Introduced component.
Related documentation
Overview
SharePoint Destination Component is an SSIS Data Flow Component for loading data into a Microsoft SharePoint list. The configuration parameters are provided in the standard Data Flow Component dialogs.
Demonstration
Quick Start
In this section we will show you how to set up a SharePoint Destination component. Note that the File Transfer Task should be used if you wish to upload files to SharePoint.
- Before you begin, drag the Data Flow Task onto the Control Flow canvas.
- Double-click on the Data Flow Task to go to the Data Flow canvas.
- Configure a SharePoint connection manager.
- Configure a source component that will provide the data to be written to SharePoint. If your input contains a Date/Time column, ensure that the datatype is set to DT_DBTIMESTAMP.
- In the SSIS Toolbox, locate the SharePoint Destination component and drag it onto the Data Flow canvas.
- Connect the blue arrow from the source component to the SharePoint Destination component.
- Double-click on the SharePoint Destination component.
- Once the component editor opens, in the Connection Managers tab, select the connection manager you configured earlier from the Connection drop-down list.
- Click on the Component Properties tab in the component editor.
- Choose the desired Action (Create, Update, or Delete). In this example, we chose the Create action.
- Choose the DestinationList you will be working with. We chose the Calendar DestinationList in this example.
- Click OK to close the component editor.
Congratulations! You have successfully configured the SharePoint Destination component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing SharePoint connection manager.
Related Topics: SharePoint Connection Manager
Select the destination list action. This parameter has the options listed in the following table.
Action Description Create Create a new item in the destination list. Update Update an existing item in the destination list. The appropriate list item identifier must be specified in order for the update to work. Delete Delete an existing item from the destination list. The appropriate list item identifier must be specified in order for the delete to work. After changing the action, you must use the Refresh command in the 'Column Mappings' tab to reload the destination list metadata.Specify the destination SharePoint list where the data is to be loaded.
Specify the number of rows to be sent as a batch. The maximum is 500.
Specify how to handle rows with errors.
Specifies how NULL values are handled. This parameter has the options listed in the following table.
Options Description True The NULL values are ignored and not sent for processing. False The NULL values are sent for processing.
Knowledge Base
- Where can I find the documentation for the SharePoint Destination?
- How to pass a multiple choices to a MULTI-CHOICE SharePoint Column?
- How to change a folder name in a Sharepoint document library.
- Error Message: 'The remote server returned an error (429)' when using Sharepoint Online.
- Error Message: The operation has timed out.
What's New
- New: Support for updating read-only fields.
- New: An enhanced configuration dialog with improved columns mapping page.
- New: A new parameter IgnoreNullValue.
- New: Component now shows user-friendly names for columns containing spaces (Thank you, Michael).
- Fixed: Component inserted incomplete date columns.
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in SharePoint components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and CozyRoc.SharePoint.dll libraries. Also include CozyRoc.SharePoint, CozyRoc.SharePoint.Lists and CozyRoc.SharePoint.Views namespaces.
IConnection / ISharePointConnection
When you call SharePoint Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with SharePoint service.
Methods
Connects to SharePoint service.
Closes the connection to SharePoint service. Use always after you finish working with the service.
Returns true if connected to the service.
Get specific SharePoint web service. You have to connect first before use. The method has one parameter, which can be one of the options listed in the following table.
| Value | Result Service |
|---|---|
| 0 | Lists Service |
| 1 | Views Service |
Returns SharePoint Online token cookie.
Properties
Specify deployment type. This property has the options listed in the following table.
| Value | Description |
|---|---|
| 0 | Connect to on-premise SharePoint server. |
| 1 | Connect to SharePoint Online service. |
Specify the name or IP address of the SharePoint server.
Specify user name to access the SharePoint service.
Specify user password to access the SharePoint service.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
| Value | Description |
|---|---|
| True | All clients use same connection (default). |
| False | Every client uses different connection. |
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
Knowledge Base
Related documentation
SharePoint Connection
- New: Experimental feature to explicitly specify authentication token when using MFA-enabled service. For further information please review 'Establish connection to MFA-enabled service' Quick Start.
- New: Support for on-premises servers that use ADFS authentication.
- Fixed: Failed to delete file with ampersand (&) special symbol in the name when using File Transfer Task (Thank you, Nandu).
- Fixed: File Transfer Task was not working properly because of different file name case.
- Fixed: File Transfer Task 'Create Remote Directory', 'Remove Remote Files', 'Rename Remote File' actions did not work properly when used with SharePoint sub-site.
- Fixed: File Transfer Task did not report failure errors.
- Fixed: Failed to connect to SharePoint Online when third-party Ping token service was used (Thank you, Pradeep).
- Fixed: File Transfer Task was not working properly when used with SharePoint sub-site (Thank you, Michael).
- New: Support for sites migrated from BPOS (Thank you, Angel).
- New: Enabled support for TLS 1.1 and 1.2 protocols.
- New: Support for Office 365 deployed systems with Federated (delegated) security (Thank you, Phil).
- New: Introduced connection.
SharePoint Destination
- New: Support for updating read-only fields.
- New: An enhanced configuration dialog with improved columns mapping page.
- New: A new parameter IgnoreNullValue.
- New: Component now shows user-friendly names for columns containing spaces (Thank you, Michael).
- Fixed: Component inserted incomplete date columns.
- New: Introduced component.
SharePoint Source
- New: An enhanced configuration dialog with improved output columns page.
- Fixed: Removed alphabetical columns sort.
- Fixed: Multiple fixes in CAML statement design dialog (Thank you, Luigi).
- Fixed: Component did not work in dynamic data flow (Thank you, Masthan).
- New: Component now shows user-friendly names for columns containing spaces (Thank you, Michael).
- New: Component now includes column with list item's attachments (Thank you, Anthony).
- New: A new parameter IncludeHidden to include list's hidden fields.
- New: Introduced component.
Knowledge Base
- Error Message: 'The remote server returned an error (429)' when using Sharepoint Online.
- Where can I find the documentation for the SharePoint Connection?
- Where can I find the documentation for the SharePoint Source?
- Where can I find the documentation for the SharePoint Destination?
- If our SharePoint service is MFA-enabled, does this mean we can't use the SharePoint components?
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.