I was wondering if there was any 'neat' way to handle the following scenario.
A file is downloaded from a 3rd Party Reporting Portal. But for some reason two reports came down with slightly different formats. The data is the basically the same but for some reason the report engine added a couple of extra columns into the middle of the report which comes down as a simple CSV file.
I suspect human intervention but the end-user is convinced nothing was done to cause this. We can manage both formats but I was wondering if there was a way to quickly determine within a Transformation that the reader file had a specific number of columns and then condition the next transformer object that is used based on that and have it happen inside the Transformation itself.
I know I could use a Package Script Action to read the Column Header Row and then use some code to figure out the number of columns and then call a different Transformation. But I was wondering I could do this is the Transformation itself and then put a field selector in place to ignore the extra columns.
What happens with "Field Value" if you specify a Field Name that is not in the input stringf?
Assuming you just get null or an empty string this would work for us. The downside for us is the inbound data has over 50 fields with verbose column names. Hence there will be quite a lengthy transformation.
Certainly "doable" assuming we get an empty string if the FieldName does not exist. In our case their reporting system generates 51 fields on one occasion and about 47 fields on another. For some reason it drops 4 of the fields. Hence, the Transformation would need to basically return an empty string or null if the field is missing.
Another approach would to have a branching condition in the transformation based on a special action that returns the number of columns returned by the reader. Then the condition could branch and run the required transformer object. Even better would be having a CASE style in case there are more than 2 file shapes.
The problem from here is somehow allowing the designer to render the inbound fields in the individual transformers depending on the branch.
