Issue Summary - Policy_EmailAddress in metmx:policy is empty for all the records in the past 6 months
We were able to identify the root cause to be Import ContactInfo Workflow is not processing any files since 08th April 2022.
We are exploring the best possible approach to fix this issue. please let me know with the detailed summary below if you can guide me on the best possible way to fix this issue.
• Policy Email Address value to be inserted into the Policy table is fetched from the Contact Info table
• Import Policy Workflow is used to load metmx:policy table
o Files are received daily and are getting processed successfully as per configured logic.
• Import ContactInfo Workflow is used to load metmx:contactInfo table
o Files are received daily but the last successful processing is on 7th April 2022.
o All the files might be lying in the SFTP location
• In Import Policy workflow, the Email Address is not available in the incoming POLIZ* file used for loading data into metmx:policy table
• In the Import Policy workflow, an outer join is formed with the following columns to identify the email address from the Contact Info table
o tipoContacto = ‘EMAIL’
• From the processed file history of Import ContactInfo Workflow, We observed that the last successful processing of MEDICO_CONTACTO file is on 7th April 2022
• As the Contact Info is not getting loaded, the join in Import Policy workflow is not yielding any result and hence Policy Email Address is getting populated as empty.
• On 8th April 2022, there seems to be an error in the main ACX File Manager workflow and this would have been restarted by the team without giving attention to the import workflow (Import Contact Info Workflow) that failed and hence the current issue.
Do you have the error that was thrown? If not, can you attempt to load the file and see what the error is? That would give the community a better sense of what might have happened.
I would set a constraint on the database level to make the email field mandatory on what ever schema is being left empty.
alter table xxx alter column columnxxx varchar not null;
Furthermore, I think you can configure the loading activity to make certain fields/columns mandatory (non empty) and therefore you could do some error handling and trigger a new alert if a violation is made whilst loading the data.