Premium SharePoint connector

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

Overview

Etlworks SharePoint premium connector allows fast, real-time access to data stored in SharePoint. The connector works just like any other database connector. It provides read-write access to the following data objects stored in SharePoint:

• Files
• Sites
• Lists
• Groups
• Users
• Attachments

Etlworks includes a free SharePoint storage connector which we recommend using if you only need to access files stored in SharePoint.

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

When to use SharePoint (premium) connector

Use this connector to create Flows that:

• Extract data from and load data into Lists.
• Extract data from and load data into Sites.
• ETL Users.
• ETL Attachments.
• Download files from SharePoint into local storage.
• Upload files in local storage into SharePoint.

Prerequisites

Enable the SharePoint connector for your Etlworks 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 (premium).

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.

ETL data from SharePoint

Using our Sharepoint connector, you can Extract-Transform-and-Load data from SharePoint Lists, Groups, Users, Attachments, and Sites into any destination.

Extracting data from SharePoint is similar to extracting data from a relational database.

Here's a detailed instruction on how to extract data from SharePoint:

Step 1. Create Sharepoint (premium) Connection that will be used as a source (FROM). 

Step 2. Create a destination Connection, for example, a Connection to the relational database, and if needed, a Format (Format is not needed if the destination is a database or well-known API).

Step 3. Create a Flow where the source is a database, and the destination is a Connection created in step 2, for example, a relational database. 

Step 4. Add new source-to-destination transformation.

Step 5. Select the Sharepoint (premium) Connection created in step 1 as a source Connection, and select the Sharepoint object you are extracting data from.

Step 6. SelectTOConnection, Format (if needed), and object (for example, database table) to load data into.

Step 7. ClickMAPPINGand optionally enterSource query(you don't need a query if you are extracting data from the Marketo object unconditionally).

Step 8. Optionally, define the per-field Mapping.

Step 9. Add more transformations if needed.

ETL data into SharePoint

Loading data in SharePoint is similar to loading data into a relational database.

Here's a step-by-step instruction on how to load data in SharePoint:

Step 1. Create a source Connection and a Format (if needed).

Step 2. Create a destination Sharepoint (premium) Connection.

Step 3. Create a Flow where the destination is a database.

Step 4. Add new source-to-destination transformation.

Step 5. SelectFROMandTOConnections and objects (also aFROMFormat if needed).

Step 6. Optionally, define the per-field Mapping.

Step 7. Add more transformations if needed.

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