Mapping Data Flows Dynamic Column Updates

Question

I have a text input source. This has over 100 columns so I won't show all of them here - a cut-down view of the data would be:

CustomerNo	DOB	DOD	Status
01418495	01/02/1940	NULL	1
01418496	01/01/1930	NULL	1

The users want to be able to update/override any of these columns during processing by providing another input text file containing the PK (CustomerNo) and the key/value pairs of the columns to be updated e.g.

CustomerNo	Variable	New Value
01418495	DOB	01/12/1941
01418496	DOD	01/01/2021
01418496	Status	0

Can this data be used to create dynamic columns somehow that update the customer records regardless of the columns they want to update - in the example above this would result in:

CustomerNo	DOB	DOD	Status
01418495	01/02/1941	NULL	1
01418496	01/01/1930	01/01/2021	0

I have looked at the documentation but don't see any examples of how something like this could be achieved? Thanks in advance for any advice.

Answer 1

You would use a technique similar to what I describe in this video: https://www.youtube.com/watch?v=q7W6J-DUuJY. What I've done is created a file with rules that have expressions and then apply those rules dynamically inside of my data flow.

The key to make this work is using the expr() function to dynamically evaluate the expression from the external file.