Schema Map
The Schema Map in Data Sync is the connection between your Source and Target Data. It maps columns between data sources and provides data type conversions.
The basic idea is that you model your Source data the way you want it to appear on the Target, and Data Sync works out how to make them the same.
The Schema Map requires a unique Key Column to determine whether to create an ADD, UPDATE, or DELETE action. If the Key is only in the Source (A), it's an ADD action. If it exists in both the Source (A) and Target (B), it's an UPDATE action. If it only exists in the Target (B), it's a DELETE action.
The Key Column can be a composite key made up of multiple columns. In this case, the values from these columns are combined to create a unique key value.
If your Data Source returns multiple rows for the same Key value, Data Sync flags them as duplicates and removes the duplicate items from the compare. Only the first row with a given Key value is part of the compare and sync.
Each column in the Schema Map defines a Data Type, and when the Data Source is loaded, the incoming data is converted into this Data Type.
During the compare phase, these data values are compared based on the Data Type.
This is useful for mapping data from different sources with different data types.
For example, if your source has a True/False value that you need to map to a SQL Integer Field, setting the Data Type to System.Boolean will automatically convert the value to 0 or 1 if the source returns "Yes" or "No" text values.
To add rows to the Schema Map, simply check them in the Data Source window.
You can also use Drag and Drop to edit the Schema Map by dragging a column from the Data Source over the mapping.
Most of the Data Connectors are optimized so that only the columns defined in the Schema Map are returned from the Target. Therefore, for best performance, you should limit the mapping to only the columns you're working with.
The Schema Map can support up to 500 columns. However, the wider your data set, the slower everything is.
At the bottom of the schema map are the column properties, you can read more about using these here.