quite often when our load strategy has failed it is because the field setup for the target fields are setup for a smaller size e.g. nvarchar(100) while the source system delivers a larger string e.g. 105. As we have PostgreSQL as a source system where only data type text without limitation is used it happens quite often that we have to do adjustments to our tables.
The error description in SQL Server is so not helping since it only informs us about the table not the field that is causing the error "field would be truncated". we then have to go through all the fields and determine the one that is causing the error. As you might imagine this is quite time consuming and frustrating as it keeps happening.
Therefore I thought that it might be a good idea to have the RAW tables fields size setup independent of valid table field size. Setting all raw table string fields e.g. to max and the all numeric fields to bigint / decimal 38,6 etc.
This way the data is loaded into the raw table already before the error is thrown. We would be able to determine much quicker which line and field is causing the error as the data would be loaded from within SQL Server enabling us to:
- have the row identified that causes the error
- have a script that could max(len(string_fields)) and compare the outcome directly with the current field setup via sys.objects
- eventually even give the user an suggestion for resolving the error by an automated routine through Discovery Hub