I'm extracting source data from mySQL and loading it into our target MSSQL database. I'd like to add a modified date to the target table that only updates when at least one of the fields have changed. What is the best approach to do this? Options I can see could be:- [list=1]
Use the tMSSqlSCD component
Add an outer join between the source and the target using tMap. Then create two outputs; one for inserts based on whether the row exists in target, and one for updates based on whether any fields have changed
Use database triggers in MSSQL
[list=*]
Option 2 seems very long winded, and quite hard to maintain if I wanted to add fields at a later date.
I don't want to use 3 as the performance would be poor.
Go to the advanced setings of the tMssqlOutput component and activate the Field Options. Here you can define which field will be used for insert and/or update.
