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
Enter your SharePoint Credentials by clicking the button on the Credentials option under Authentication (needed because CFS requires Basic Authentication)
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
Select the Entity to connect to from the Entity Dropdown. Data Sync will query the Capturx OData interface and return all the available lists.
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.
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.
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)
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.
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)
Choose the Database and enter a Name for the SQL Table
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.
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
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.
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.
Click the Synchronise Button to start the process of copying the data to the SQL Table.
After a few seconds the process will complete and if successful the Log will just say “Completed”.
You can the check/preview the data by pressing Preview B on the Schema Map to return the data in the SQL Table.
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?
- Contact our support team and we'll personally get back to you as quickly as we can.