The below tutorial will cover how to configure your schema map to your specifications.
Once you are connected to your source and target, you can configure which columns you wish to synchronise across to your target and how these link to one another.
Make sure you define a key column and include all of the columns you wish to synchronise.
The below example is a simple CSV to SQL Database:
The columns from the source have been mapped to corresponding columns in the target.
You can add columns to the schema map by checking the checkbox next to the column in the source or target, or you can drag and drop this into the schema map.
If this is not automatically mapped or the mapping is incorrect you can change the target column by choosing the column from the drop-down menu or by dragging the column from the target onto the correct column in the schema map.
If you wish to remove a column, select the column and then either click
Delete in the toolbar or press delete on your keyboard.
The tool bar across the top of the schema map tab contains the following functions:
Where you can highlight a column and move this up the list.
Where you can highlight a column and move this down the list.
Where you can highlight a column and remove this from the schema map.
Where you can preview the data contained in your source, showing only the columns defined in the schema map.
Where you can preview the data contained in your target, showing only the columns defined in the schema map.
If your Data Source returns multiple rows for the same Key value, they are flagged as duplicates and the duplicate items are removed from the compare. Only the first row with a given Key value is part of the compare and sync. Therefore your key column needs to be unique.
This can be a composite key made up of multiple columns when a single unique key column field is not available. In this case the value(s) from these columns are combined to make a unique key value.
Each column in the Schema Map defines a Data Type, when the Data Source is loaded the incoming Data is converted into this Data Type and during the compare phase these Data values are compared based on the Data Type.
This is useful where your source my have a True/False value that you need to map to a SQL Integer Field. If your Source returns "Yes" or "No" text values, setting the Data Type to System.Boolean will automatically convert the value to 0 or 1.
The allow null column of the schema map enables you to set whether you will accept null values for any given field. If your target system does not accept nulls into the column you are mapped to then un-check the allow null checkbox.
The Data Compare Settings are used to control the comparison of data in Data Sync. You can change this on a column by column basis in the Schema Map.
The Column properties can be found below the schema map:
The properties relating to the column directly.
Is the column a dynamic column? If yes then this will be
True. If not then this will be
What is the maximum number of characters you wish to allow in each field of this column.
The name of the column you are editing the properties of.
Does the column have read only permissions. If yes then this should be set to
True, otherwise it will be
The properties relating to how the column should be handled during the comparison of the column.
The Ignore setting will prevent Data Sync comparing the value and generating an UPDATE action for this column, however if any other column causes an UPDATE action then the value of this column will be added to the change set. This is useful when you want to force a columns value to always be sent to the target.
The Data Compare Mode is used to control how the source and target values are compared for UPDATE actions. The default comparison mode is to compare that the values from each side are equal.
The data compare mode is typically used in conjunction with the Trigger Update setting. This setting is used to decide what change(s) will cause an UPDATE action to be created.
For example in 2-Way sync where you have a modified DateTime value on each side which is updated when the row is changed. You would create 2 projects each running in a different direction.
The First project you would set TriggerUpdate=False on all columns in the Schema Map except for Modified and then set the Mode=TargetLessThan for the Modified Column. This causes Data Sync to only compare the values on this row when then Target Modified value is less than the source. i.e. the Source is more recent.
The Second project can now be setup as normal but in reverse, you then set the projects to run after each other in Ouvvi or the Run Tool.
The properties relating to the column if it is a lookup column.
Is the column a lookup column? If yes then this will be
True. If not then this will be
The name of the lookup column.
The name of the lookup, this is determined by the user when configuring the lookup parameters.
If you get the following error message appear when you go to compare the data:
This means you have not defined a key column. To resolve this go back to your schema map and check the key column box for your key column.
If you get the following message appear in the compare window:
and a message similar to this in the output window:
Then you do not have unique key values for your data, please go back and review your data to ensure that each record has a unique identifier.
If you get an error message similar to the one below appear when you go to compare the data:
Then it means you do not have the right data type for columns, please go back and review your data and the columns corresponding data types. For example this could be trying to insert string data into a int32 field.