0

I'm working on a scenario where I have to compare a data record which is coming from a file with the data from a table as part of validation check before loading the data file into the staging table. I have come up with a couple of possible scenarios which involve something that needs to change within the load mapping, but my team suggested to me to make a change to something that is easy to notice since it is a non-standard approach.

Is there any approach that we can handle within the workflow manager using any of the workflow tasks or session properties?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Meet Rohan
  • 75
  • 2
  • 13
  • 1
    The Workflow Manager can't read data from a table (a command can read from a file), but can examine the status or success/error rows of a mapping. I would create another mapping to run before loading to do the comparison, and then use its results to decide to launch the loading mapping or not. – Mickaël Bucas Jan 10 '20 at 08:55

2 Answers2

1

Create a mapping that will read the file, join data with the table, do the required validation and will write nothing out (use a filter with FALSE condition) and set a variable to 0/1 to indicate if the loading should start.

Next, run the loading session if the validation passed.

This can be improved a bit if you want to store the validation errors in some audit table. Then you don't need a variable - the condition can refer to $PMTargetName@numAffectedRows built-in variable. If it's more then zero - meaning there were some errors - don't start the load.

Maciejg
  • 3,088
  • 1
  • 17
  • 30
-1

create a workflow with command line where you need to write a script which will pull the data from the table by using JDBC connections and try to compare with data present in the file and then flag whether to load or not .

based on this command line output you need to go ahead with staging workflow or not..

Use awk commands for comparison of the data , where you ll get flexibility to compare date parts in a column

FYR : http://www.cs.unibo.it/~renzo/doc/awk/nawkA4.pdf

shaikshare
  • 1
  • 4