United Kingdom +44 (0) 1424 863 450   hello@simego.com

Export Capturx Data from SharePoint

This guide shows how to configure an export synchronisation of Data Stored within the Capturx


15 October 2013

This guide shows how to configure an export synchronisation of Data Stored within the Capturx for SharePoint system with a SQL Server Database Table for reporting or archive purposes. The same process can be used with different target data sources such as Oracle DB, CSV, XML Files, CRM Server etc.

Step 1 – Create new Project and Choose the OData Provider for the Data source A connection.

New Project > Connect to Data Source > Text Files > OData

clip_image002

Enter your SharePoint Credentials by clicking the button on the Credentials option under Authentication (needed because CFS requires Basic Authentication)

clip_image004

Enter the URL to the Capturx OData endpoint in the ServiceUrl property which will be something like this.

Service URL – mysite/subsite/_vti_bin/Capturx/Capturxdata.svc

clip_image006

Select the Entity to connect to from the Entity Dropdown. Data Sync will query the Capturx OData interface and return all the available lists.

clip_image008

Once you click OK, Data Sync will return the available columns form the Capturx OData Service and create a default schema map that contains these columns.

clip_image010

 

If you have a View in SharePoint that returns a filtered result you can configure the View to use with the ServiceCommand setting. Simply set the Service Command to View=ViewName as in the example below. If you change this setting you must click the refresh icon on the Datasource to refresh the schema.

 

image

 

Optionally remove columns that are not required. (Data Sync requires a Unique Key column so it is best to leave the ID column from the Source as the Key between the records)

clip_image012

Step 2: Create the Target (Data source B) SQL Table

To create a SQL Table to hold this data is a simple process, simply use the Create SQL Table Wizard on the Tools Menu to let Data Sync create the Table Schema automatically for you.

clip_image014

clip_image016

Enter the name of your SQL Server where you want to create the Table to store the data from the Capturx OData Service. (If the SQL Server is remote change the Network Library to TCP/IP)

clip_image018

Choose the Database and enter a Name for the SQL Table

clip_image020

Data Sync will now Create the SQL Table and Load the SQL Table as the Target in the UI. The Schema Map will be created for you.

clip_image022

Step 3: Map and Synchronise

Optionally set Deletion to false on the SQL Target, if transfer is meant to be unidirectional (removing items from the list does not remove them from the repository). Useful if the list items are archived/removed after some time

clip_image024

Click Compare A->B to load the Data from Capturx OData Service and SQL Table, Data Sync will then work out what needs to be either ADDED or UPDATED in the SQL Table.

clip_image026

After the Compare has completed, Data Sync will present the results where you can see how many items need to be either ADDED or UPDATED in your SQL Table. You can click on either set in the results list to preview the data before sending it to your Database.

clip_image028

Click the Synchronise Button to start the process of copying the data to the SQL Table.

clip_image030

After a few seconds the process will complete and if successful the Log will just say “Completed”.

clip_image032

You can the check/preview the data by pressing Preview B on the Schema Map to return the data in the SQL Table.

clip_image034

The Data Sync Project can be saved and scheduled to run at a specific time schedule either with Windows Task Scheduler, SQL Agent or Simego Automation Server (Ouvvi).


|
Can't find the answers you're looking for?