Use CloverDX (formerly CloverETL) and CData JDBC Drivers to Transfer Data

The CData JDBC Drivers work with any tool that supports JDBC, including CloverDX (formerly CloverETL). This article shows how CData drivers can be used from CloverDX, a data integration tool, to transfer data between two data stores.

In this tutorial, you will use the CData Google and SharePoint JDBC Drivers to transfer a Google calendar to a SharePoint calendar. Before getting started, you will need test calendars in both SharePoint and Google. You will use SharePoint Online as the destination, but our driver for SharePoint supports both Office 365 and OnPremise versions.

In the procedure below, you will first extract data from Google by selecting columns from the CalendarEvents table. You will then assign aliases to match the column names of the table for your Calendar app in SharePoint. Finally, you will create a DBOutputTable that will load the data to SharePoint.

1. Create the connection to Google. In a new CloverDX project, right-click the Connections node in the Outline pane and click Connections -> Create Connection. In the resulting Database Connection wizard, create a new JDBC driver definition. Click the plus icon to load a driver from a JAR and then browse to the lib subfolder of the installation directory. Select the cdata.jdbc.google.jar file. Enter the username, password, and the JDBC URL. A typical JDBC URL for connecting to Google is below:

   jdbc:googleapps:user=myuseraccount;password=mypassword;

2. Create the connection to SharePoint. Right-click the Connections node in the Outline pane and click Connections -> Create Connection. Load the SharePoint JAR and enter the required connection string properties. A typical JDBC URL for connecting to SharePoint Online is below:

   jdbc:sharepoint:User='user';Password='password';URL='https://yoursite.sharepoint.com';SharePoint Edition='SharePoint Online';UseDisplayNames=False;CalculatedDataType=String;

3. Drag a DBInputTable from the Readers selection of the Palette onto the jobflow and double-click it to open the configuration editor. In the DB connection property, select the Google JDBC data source from the drop-down menu. Next, enter the SQL query. The query below selects several columns and assigns aliases that match the column names for a SharePoint calendar:

   SELECT StartDateTime as [EventDate], EndDateTime as [EndDate], Summary as [Title], Description, Location FROM CalendarEvents WHERE CalendarId=@ID

4. Drag a DBOutputTable from the Writers selection onto the jobflow. Double-click it to open the configuration editor. Select the CData JDBC Driver for SharePoint and in the SQL query property enter the query below:

   INSERT INTO Calendar (EventDate, EndDate, Title, Description, Location) VALUES (?, ?, ?, ?, ?)

5. Right-click the DBInputTable and then click Extract Metadata. Connect the output port of the DBInputTable to the DBOutputTable. In the resulting Select Metadata menu for the DBOutputTable, choose the CalendarEvents table. (You can also open this menu by right-clicking the input port for the DBOutputTable.) Click Run to transfer the calendar events from Google to SharePoint.