This guide shows how to configure an export synchronisation of Data Stored within the Capturx for SharePoint system to 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.
Open a new Data Sync project and connect the source to the OData provider.
Enter the URL to the Capturx OData endpoint in the ServiceUrl property which will look something like this
Now enter your SharePoint credentials under the Credentials property and then 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 Connect, Data Sync will return the available columns form the Capturx OData Service and create a default schema map containing 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. This can be found in the properties winodw below the source columns.
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.
You can also add and remove columns to the schema map to make sure only the data you need is included in the synchronisation. 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.
To create a SQL Table to hold this data is a simple process, simply use the Create SQL Table Wizard in the Tools Menu. Follow through the wizard to connect to your SQL Server where you want to create the table.
If the SQL Server is remote change the Network Library to TCP/IP
Data Sync will then Create the SQL Table based off of your source schema and load it as the Target in the UI.
You can optionally set deletion to true or false on the SQL Target, by default this is set to false. If the transfer is meant to be unidirectional, the delete set to false will man that removing items from the list does not remove them from the repository. This is useful if the list items are archived/removed after some time.
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.
Synchronise to start the process of copying the data to the SQL Table.
You can the check/preview the data by pressing Preview B on the Schema Map to return the data that should now be 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).