- 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 Formathttps://organization.sharepoint.com/
.Authentication Method
: select eitherOAuth
(default) orUsername 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.
Comments
0 comments
Please sign in to leave a comment.