Hi all - In short, our 'Table Comparison' Transforms in BODS (4.2 SP14 Patch 5) aren't updating target table tables with changes from the source input tables. Any helpful advice would be much appreciated from anyone. Fully explained below:
We recently migrated our main ETL repository from BODS XI (126.96.36.199) over to BODS 4.2 (SP14 Patch 5) and upgraded it using the Repository Manager. We’ve had the newly migrated job (BODS 4.2) running against a test database for some time and during data reconciliation testing (comparing to the production data generated by our Legacy Server) we found that all of our Table Comparison Transforms have a common issue where not all of the updates from source to target are being applied. The exact / identical replicated data flow in the Legacy environment parses and updates the records as expected, but in the new environment it does not. We’ve tried re-creating the data flow’s from scratch as well as inputting different values e.g. smaller text only or numeric values only with no spaces (without any success). This issue is repeated across many other data flows as this is a common step used in the majority of our Data Warehouse tables. We suspect this may be a bug.
Replication Setup (is as follows)
Transform used: Table Comparison
Table [A] (Source): PREP_PATIENT_UPDATES
This is a staging table containing patient records from the previous day which were modified in the source system that are needing to be compared to Table [B] for any updates to existing records
Table [B] (Target): PREP_PATIENTS
This table contains all patient records (master table)
CompareTable (Table Comparison Transform)
This compares the incoming records from Table [A] to Table [B], Table [B] should be updated with any incoming changes as a result of the comparison. This works perfectly in the Legacy (XI) environment.
Execute Job containing same identical dataflow in both environments. The record is correctly updated to “LONDON” in the legacy environment [DS XI (188.8.131.52] but not in the modern version [DS 4.2 SP14 Patch 5], the record stays the same.
If anyone knows of a particular fix or release that is recommended / stable without this issue that would be awesome to know as I'm going to start testing out different releases on another server to see if it has this issue.
We can of course avoid using the ‘Table Comparison’ transform but that would that would require a large amount development / re-work to re-design all of our data flows.