SharePoint connector with OAuth2

• Startup
• Business
• Enterprise
• On-Premise
• Add-on

Etlworks SharePoint with OAuth2 connector allows fast, real-time access to files stored in the SharePoint. The connector works just like any other database connector.

Etlworks Integrator includes a free SharePoint storage connector that doesn't support OAuth2 authentication but is recommend for all other scenarios.

Etlworks partnered with CData to provide access to SharePoint using industry-standard JDBC protocol. Read about CData SharePoint JDBC connector. 

When to use Consumer SharePoint connector

Use this connector to create Flows that download files from and upload files into SharePoint.

Prerequisites

Enable the SharePoint connector for your Etlworks Integrator account. Contact support@etlworks.com to enable the connector.

Create a Connection

Here's how you can create a Connection in two easy steps:

Step 1. In the Connections window, click +, and type in sharepoint and select SharePoint.

Step 2. Enter Connection parameters:

• SharePoint URL: enter the SharePoint URL in the Format https://organization.sharepoint.com/.
• Authentication Method: select either OAuth (default) or Username and Password.

When using OAuth authentication (default):

• Permissions: select the permissions that will be used to access data in SharePoint. 
• OAuth Token: sign in with Microsoft.

When using Username and password authentication:

• Authentication Scheme: select the authentication scheme.
• User: the user name.
• Password: the password.

Use Other parameters to specify the Connection string options. Read about available Connection string options.

Work with SharePoint

SharePoint Data Model

The connector models SharePoint entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your SharePoint site. Any changes you make, such as adding a custom field or changing a field's data type, are automatically reflected when you connect.

Read about the SharePoint data model.

Stored procedures

Stored procedures are available to complement the data available from the SOAP Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the data retrieval is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return a dataset that contains the collection of tuples that constitute the response.

Read about available stored procedures in the data model.  

To call stored procure from the SQL Flow or Before/After SQL use EXEC sp_name params=value. Example:

EXEC DownloadFile ResourcePath='path',LocalFile='file' 

SQL Compliance

Read about SQL Compliance.

Download document from the SharePoint library

Here's how you can do this:

Step 1. Create SharePoint Connection.

Step 2. Create a new SQL Flow.

Step 3. Select a Connection created in step 1.

Step 4. Select the Parameters tab and enter the following SQL:

EXEC DownloadDocument File='file',Library='library', RemoteFile='remotefile' 

Where File is a path of the file in local storage, for example, {app.data}/test.json, and Library is the name of the library on the SharePoint server, and RemoteFile is the path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded.

Download document from the SharePoint list

Here's how you can do this:

Step 1. Create SharePoint Connection.

Step 2. Create a new SQL Flow.

Step 3. Select a Connection created in step 1.

Step 4. Select the Parameters tab and enter the following SQL:

EXEC DownloadAttachment File='file',RemoteFile='remotefile' 

Where File is a path of the file in local storage for example {app.data}/test.json, and RemoteFile is the path of the file on the server. This can be the full URL or simply the file name. If you use the name of the file, the latest version will be downloaded.

Upload file to SharePoint library

Here's how you can do this:

Step 1. Create SharePoint Connection.

Step 2. Create a new SQL Flow.

Step 3. Select a Connection created in step 1.

Step 4. Select the Parameters tab and enter the following SQL:

EXEC UploadDocument File='filename', Library='library', Name='remotefile' 

Where File is a location of the file in the local storage, for example {app.data}/test/json, and Library is the name of the library on the SharePoint server, and Name is the path of the file on the server.

Browse folders and files in SharePoint

You must have SharePoint Connection to browse objects and run SQL queries.

Use the Etlworks Explorer to browse data and metadata in SharePoint as well as execute DML and SELECT queries against the SharePoint Connection.