Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →How to Work with SharePoint Data in ETL Validator JDBC
Connect to SharePoint from ETL Validator jobs using the CData JDBC Driver.
ETL Validator provides data movement and transformation capabilities for integrating data platforms across your organization. CData's JDBC driver seamlessly integrates with ETL Validator and extends its native connectivity to include SharePoint data.
This tutorial walks through the process of building a simple ETL validator data flow to extract data from SharePoint data and load it into an example data storage solution: SQL Server.
About SharePoint Data Integration
Accessing and integrating live data from SharePoint has never been easier with CData. Customers rely on CData connectivity to:
- Access data from a wide range of SharePoint versions, including Windows SharePoint Services 3.0, Microsoft Office SharePoint Server 2007 and above, and SharePoint Online.
- Access all of SharePoint thanks to support for Hidden and Lookup columns.
- Recursively scan folders to create a relational model of all SharePoint data.
- Use SQL stored procedures to upload and download documents and attachments.
Most customers rely on CData solutions to integrate SharePoint data into their database or data warehouse, while others integrate their SharePoint data with preferred data tools, like Power BI, Tableau, or Excel.
For more information on how customers are solving problems with CData's SharePoint solutions, refer to our blog: Drivers in Focus: Collaboration Tools.
Getting Started
Add a new ETL Validator data source via CData
CData extends ETL Validator's data connectivity capabilities by providing the ability to add data sources that connect via CData's JDBC drivers. Connecting to SharePoint data simply requires creating a new data source in ETL Validator through CData's connectiviy suite as described below.
Login to ETL Validator
Begin by logging into ETL Validator to view the application dashboard.
Click on Add a DataSource
CData extends the data source options within ETL Validator.
Click on CData
CData's connectivity is embedded within ETL Validator's data source options.
Configure the CData Driver Connection String
You will need a JDBC connection string to establish a connection to SharePoint in ETL Validator.
Set the URL property to the base SharePoint site or to a sub-site. This allows you to query any lists and other SharePoint entities defined for the site or sub-site.
The User and Password properties, under the Authentication section, must be set to valid SharePoint user credentials when using SharePoint On-Premise.
If you are connecting to SharePoint Online, set the SharePointEdition to SHAREPOINTONLINE along with the User and Password connection string properties. For more details on connecting to SharePoint Online, see the "Getting Started" chapter of the help documentation
Built-in Connection String Designer
For assistance in constructing the JDBC URL, use the connection string designer built into the SharePoint JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.
java -jar cdata.jdbc.sharepoint.jar
A typical connection string looks like this:
jdbc:sharepoint:User=myuseraccount;Password=mypassword;Auth Scheme=NTLM;URL=http://sharepointserver/mysite;SharePointEdition=SharePointOnPremise;
Licensing the Driver
To ensure the JDBC driver is licensed appropriately, copy the license file to the appropriate location:
Copy the JDBC Driver for SharePoint and lic file from "C:\Program Files\CData[product_name]\lib" to
"C:\Datagaps\ETLValidator\Server\apache-tomcat\bin".
cdata.jdbc.sharepoint.jar
cdata.jdbc.sharepoint.lic
Note: If you do not copy the .lic file with the jar, you will see a licensing error that indicates you do not have a valid license installed. This is true for both the trial and full versions.
Save the connection
Should you encounter any difficulties loading the CData JDBC driver class, please contact DataGap's team, and they will provide you instructions on how to load the jar file for the relevant driver.
Add SQL Server as a Target
This example will use SQL Server as a destination for SharePoint data data, but any preferred destination can be used instead.
Go to DataSources and select MS_SQL_SERVER
This option is the default.
Fill in the necessary connection details and test the connection
The details will depend on the specific target, but these details may include a URL, authentiation credentials, etc.
Create a Dataflow in ETL Validator
Open the Dataflows tab
Configured data flows will appear in this window.
Select Create Dataflow
Name your new dataflow and save it.
Open the Dataflow to view the Dataflow Diagram
The details of the data movement will be configured in this panel.
Drag & drop the JDBC as a source from the right side
Give the new source an appropriate name and save it.
Fill in the Query section of the new source
Select the Table from the Schema option that reflects which data should be pulled from SharePoint data.
View the expected results of your query
The anticipated outcome of the configured query is displayed in the Result tab.
Add the destination to the Dataflow
Select Switch to Diagram, then drag & drop the DB Sink as a target from the right side (under Sink options). Give the sink an appropriate name and save it.
Set the appropriate Schema for the destination
Choose the Schema and table that matches the structure of the source table. For this example, the table on the target side was created to match the Source so that data flow seamlessly. More advanced schema transformation operations are beyond the scope of this article.
Hit the RUN option to begin replication
Running the job will take some time.
View the finished Dataflow
Return to the diagram to see the finished data replication job from SharePoint data to SQL Server.
Get Started Today
Download a free, 30-day trial of the CData JDBC Driver for SharePoint and start building SharePoint-connected applications with ETL Validator. Reach out to our Support Team if you have any questions.