When using the Jet Data Manager to connect a data source to a pre-built Data Warehouse structure or change a data source connection, some of the data types might not match up. This can result in errors during execution or data being truncated / left out of the data warehouse table.
Note: Extreme care should be taken in overriding standard project fields, as this can cause adverse results
Right click the data warehouse and select Synchronize Data Types.
The feature can also be used on an individual table level:
The feature will check if the data type on the data warehouse table matches the data that is copied into the field from the source of the copy. If it encounters any differences it will try to determine and suggest the best suited data type.
The suggestion and the data types of the source(s) are shown in the dialogue:
Clicking the OK button will accept all inputs and modify the corresponding fields in the data warehouse.
Overriding Data Types
You have the option to bring data into your data warehouse from a wide (and every growing) variety of data source. These sources can vary wildly but, when you consolidate them all in one data warehouse, all data needs to have a data type that conforms to the Microsoft SQL Server and is optimized for later use.
Jet Data Manager version 2017 and higher includes a data type override feature to handle tasks like these.
For each data source you can add rules on any combination of schema, table, field, and data type. You can order the rules to create a hierarchy that ensures every field in the data source ends up with the
correct data type. Even the default rule that makes sure any unknown data types are converted into a type SQL Server can understand is explicitly represented.
You can find the Data Type overrides feature in the Data Source Settings window (right click-your data source and then click Data source Settings):
You can then define how you want the new data treated.
Once you have completed defining your Data Type Override, be sure to then re-sync your data source to get the change to take effect: