Documentation forTask Factory

Task Factory SharePoint

Note:  Task Factory supports SharePoint versions 2010 through 2019.

SharePoint Connection Manager

The SharePoint Connection Manager is used to set up a connection to a SharePoint server. Used with SharePoint Source and SharePoint Destination. The SharePoint Documents Task requires the similar SharePoint Documentation Connection Manager.

Connection Settings

Task Factory Sharepoint Connection Connection Settings

Option Description
Server URL URL of the SharePoint server.
Deployment You have three options for the deployment:

  • Premise
  • Online
  • Claims Based Authentication
User Name The user name of the SharePoint server.
Password Password for the server.
Timeout Time in seconds before the connection times out if the connection fails.

Proxy Settings

Task Factory Sharepoint Connection Proxy Settings

Option Description
Use Proxy Selecting this allows you to connect through a proxy.

Note:  If checked you must fill in the proxy information located below the check box.

SharePoint Documents Connection Manager

The SharePoint Documents Connection Manager is used with the SharePoint Documents task.

Connection Settings

Option Description
Server URL URL of the SharePoint server.
Deployment

You have three options for the deployment:

  • Premise Online

  • Claims Based

  • Authentication
User Name The user name of the SharePoint server.
Password Password for the server.
Timeout Time in seconds before the connection times out if the connection fails.

Proxy Settings

Option Description
Use Proxy

Selecting this allows you to connect through a proxy.

Note: If checked you must fill in the proxy information located below the check box.

SharePoint OAuth2 Connection Manager

Used with the SharePoint Source and SharePoint Destination data flow components.

Connection Settings

Option Description
Client Id The Client ID for your SharePoint connection. The Client ID is found in your Azure Portal.
Client Secret The Client Secret for your SharePoint connection. The Client Secret is found in your Azure Portal.
Access Token Select Get Token to open the Token Getter window.
Is Bearer Token? Select this option if the Access Token is a bearer token.
Access Token Expiration Date

The expiration date of the access token.

Note: This settings is optional.
Security Protocol

The Security Protocol used by the API. The Default option uses the TLS version used by your .NET version by default. See the following chart for more information:

.NET Version TLS Support Information
.NET 4.6 and above Supports TLS 1.2 by default.
.NET 4.5

TLS 1.2 is supported, but you need to opt-in to use it. Execute the following code before making a connection to a secured resource to make TLS 1.2 the default:

  • ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12

.NET 4.0

TLS 1.2 is not supported. If you have .NET 4.5 (or above) installed on the system, you can opt in for TLS 1.2 even if your application framework doesn’t support it. SecurityProtocolType in .NET 4.0 doesn’t have an entry for TLS1.2, so you need to use a numerical representation of this enum value:

  • ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;

.NET 3.5 or below TLS 1.2 is not supported, and there is no workaround. Upgrade your application to a more recent version of the framework.
Site Id

The Site Id found at https://my_org.sharepoint.com/sites/MY_SITE/_api/site/id.

Note: Site Id is a mandatory requirement. Not entering the Site Id will result in a connection error.

Token Getter

Option Description
Client Id This field should be auto-completed from the previous window with your Client Id.
Client Secret This field should be auto-completed from the previous window with your Client Secret.
Directory (tenant) ID The Directory (tenant) ID of the Azure app.
GraphDefault Select this option to enable the GraphDefault option.
OfflineAccess

Select this option to enable the offline access option.

Warning: OfflineAccess is required if you are using a Refresh Token. Without OfflineAccess enabled, the component will fail when the Access Token expires.

Site.FullControl.All

Select this option in your Azure Portal to enable access to the lists and documents in your SharePoint instance.

In your Azure Portal, go to App > API Permissions > Request API Permissions > SharePoint and enable Site.FullControl.All.
Port for response

The port on your local machine where you want to send the token request response.

Warning: You will need to add this port in your Azure Portal at portal.azure.com.

Set your Redirect Uri to:

Use this as your Redirect Uri in your OAuth2 App settings.

Warning: You will need to add this redirect uri in your Azure Portal at portal.azure.com.

Get Access Token Opens a browser window to login to your Microsoft account and grant access.

Headers

You can create header names and values by entering the information in their corresponding windows.

Refresh Token

Option Description
Refresh Token

Authentication information provided to you at the application's developer site.

Note: This should match the API Key from the Connection Settings window.
Token Request Url

The URL that returns a refresh token.

Example: https://api.citrixonline.com/oauth/access_token

Headers Allows you to add headers within the refresh token. Select Add New Header to add a header. Select Remove header to remove a header.
Results Returned In Specifies how results are returned from the application (JSON, XML, or String.)
Access Token Path The token path for the access token. Example: access_token
Refresh Token Path The token path for the refresh token. Example: refresh_token
Group Index

Identifies the index position to return (0 based).

Note: This option is available when you select String for the Results Returned In option.
Use Token Store This option creates a file on your machine that saves the access token, and refresh token in an encrypted format.
Token Store Id Identifies the Token Store Id.
Token Store Path (Optional) Identifies the Token Store path.
Test Get Refresh Token Select this option to test the retrieval of the Refresh Token.

Client Certificate

Option Description
Use Client Certificate Select this option if you want to use the client certificate.
Certificate Store Location The client store location.
Search For Certificate

Enter a certificate that you want to search for and then select Find.

Note: Leave this section blank and select Find to view all certificates.
Certificate Thumbprint The identifier thumbprint of the selected certificate.

Proxy

Option Description
Proxy Host When connecting to an endpoint using a proxy, you should enter the proxy URL in this field.
Proxy Port The port number that corresponds to the URL proxy host.
User Name The user name needed to authenticate to the proxy.
Password The password that allows you to authenticate to the proxy.

Advanced Options

Option Description
Ignore Ssl Certificate Verification

Selecting this option ignores the SSL Certificate notification from the API during the connection.

Note: This option applies to APIs that require you to have an SSL Certificate. Select this option if the SSL Certificate is not needed.
Follow Redirects Selecting this option implements a 301 redirect on configured endpoints. For example, this option would automatically send your request to an HTTPS redirect once the request has reached the HTTP version of the site.

SharePoint Documents Task

Task Icon Task Description
Task Factory Sharepoint Documents Task Icon The SharePoint Documents Task gives you the ability to upload and download documents from a SharePoint documents list in SSIS. See the SharePoint Connection Manager to learn more about setting up the connection manager.

Task Factory Sharepoint Documents Task

Option Description
Connection Select an existing or create a new SharePoint Documents Connection Manager.
Task Mode There are four modes in which you can use the SharePoint Documents Task:

  • Upload File - Upload a single file to the documents list.
  • Download File - Download a single file to the documents list.
  • Upload Directory - Upload a directory of files from the local machine to a SharePoint documents lists.
  • Download Directory - Download a list of documents from a SharePoint documents list.
Check out / Check in file When selected, the file(s) are marked as Checked Out when downloaded from or Checked In when uploaded to Sharepoint to the account executing the package.
Documents List The SharePoint list that is used in this task. Must be a documents list.
SP Document Path This is the document subfolder in which to upload / download documents from, or the full path in which to download a file from. Selecting the ellipsis (...) opens the SharePoint Documents Browser.
Local File Path / Local Directory The local directory or path in which to download or upload files from or to.

SharePoint Source

Source Icon Source Description
Task Factory Sharepoint Source Icon The SharePoint Source is used to retrieve data from a SharePoint object within an SSIS package. See the SharePoint Connection Manager to learn more about setting up the connection manager.

Task Factory Sharepoint Source

Option Description
Select Connection Select an existing SharePoint Connection Manager or choose to create a new one.
Select List Once your connection manager is set, choose a list from your SharePoint server.
Select View (optional) Choose the view to use for the chosen list.
Download Attachments? Enabling this option allows users to download files as attachments within lists. Select the ellipsis in its corresponding window to define the destination path for the file.
Include Subfolders Choosing this option shows the subfolders that are part of a documents list in the result set.
Include Hidden Columns Choosing this option retrieves data from all of the columns of a list / view.
Remove ID's from Results Choosing this option removes identifier data from columns that contain both the identifier and text data of a column.
Steps
  • Choose Columns - This tab is used to choose which columns from the source are part of the output from the SharePoint Source.
  • Filter & Preview Rows - The filter control allows the user to create custom filters on the data returned from the server.

Filter

Task Factory Sharepoint Source Filter

Users can begin configuring filters by selecting the + beside the word And. Column filters are configured by selecting the blue hyperlink column name ( [Start Time] ). Operators are configured by selecting the green operator selector (for example, Is greater than or equal to).

Use Advanced Caml Editor

Selecting the Use Advanced Caml Editor option enables the Query CAML and Query Options CAML windows.

Task Factory Sharepoint Source Filter Use Advanced Caml Editor

Option Description
Query CAML The Sharepoint Source allows users to configure Collaborative Application Marchup Language. CAML is similar to XML and used to perform query operations on lists.

Note:  You must surround queries with <Query></Query> tags.
Query Options CAML Users can add options to their CAML queries.

Note:  You do not need to add the opening and closing query options tag in the query options editor.
Additional Information: For more information about using CAML, please see this Query schema MSDN article .

SharePoint Destination

Destination Icon Destination Description
Task Factory Sharepoint Destination Icon The SharePoint destination is used to send data to a SharePoint object within an SSIS package. See the SharePoint Connection Manager to learn more about setting up the connection manager.

Task Factory Sharepoint Destination

Option Description
Connection Select an existing connection manager or choose to create a new one.
Destination List Choose the destination list from the drop down menu, this is where the data is sent.
Action Once a list is chosen, you may select from three actions:

  • Create - This action creates rows in the selected list
  • Update - This action updates rows in the selected list.
  • Delete - This action deletes rows in the selected list.
Batch Size
 		Set the batch size, limited to 200.
 Map SharePoint Destination Columns
 		Once your destination is selected you can map the Input and Destination columns.
Additional Information:  See the Task Factory Error Row Handling article for more information about this functionality.