The Four Pillars of Data Quality ManagementNovember 14, 2017
Where will the Degenerate Dimension’s data stored?November 22, 2017
There is no ready made out of the box option from Informatica Power Center to address this. Based on the value in the footer, you can use only Informatica or only UNIX/Windows scripts (shell or bat) or both. In most of the practical cases, it ends up with a shell script or a command to remove the footer.
Case 1: Last line of the file is always a footer
If this is a confirmed case, then it is ideal to use the shell/bat along with some other logic (if required) to remove the last line.
There are multiple commands which can help you do that. You can use a simple tail -1 or a SED command to accomplish this. The commands can be used in the post session commands or the command task based on the need.
But how do you know that the last line is a footer and not an actual record which got truncated due to some technical issues. We cannot know that, until unless you are told or you manually open the file and check that.
Case 2: Case 1 + Check if the value is a static value
This is a very rare case that the footer is always static with some fixed value or a note saying ‘this is the end of file’.
If this is the case, then use filter transformation to filter the record with the static value.
In some cases, where you have more records to be routed to rejection or skipped or load them in to exception tables for Reconciliation then you can also go with Router transformation as well. This is based on the design and the requirement.
Case 3: Case 1 + Check if the value is dynamic
This is a usual case, where the last record in the file is a dynamic like the date on which the file is generated or the timestamp or the total number of records or the email or the contact person’s details and in some cases it is all of them.
This is not a static or a fixed value. It keeps changing every time the file is generated.
In this case, it is always a good option to go with the shell/bat scripts. Why?
Let’s say there are 20 attributes for which you are getting the data for. And the max length of all 20 attributes is 1024 bytes and the min length is about 200 bytes. Now, take the max length of footer and based on the output you can skip the values.
What if there is a case, where the actual data’s length is less than footer. Here you can set an alert or an email notification for the support team to verify the file and process it.
There is another option to verify each record and confirm if that is the last record or not and filter the last record. But if you do not consider the value of it is again a question if we are considering the right record as a footer or not.
There are always other possibilities based on the requirements and the need.
The option provided in the target session properties is to add the footer to the output file generated by Informatica, but not to remove it.
So, what’s the solution?
Try to have the file clean before it is sourced to Informatica using the shell/bat scripts.