Preface

On September 10th, 2020, the step 1 of the Unit4Integration - Inbound (Step 0 and 1) SQL Server Job got stuck indefinitely in Processing state. The attempt to stop the job and run Unit4Integration - Inbound (Step 2) job to generate the output OMH file ended up with an empty file.

Investigation

The investigation shown that the ScriptComponent task of Calculate Payable Amount (part 1b).dtsx package had thrown an exception that had not been handled by the task, and thus, caused the whole package execution to get into hanging state. The error was about some unexpected NULL values in the OMFH_ClaimPaid table (CUSTOM database).  The table OMFH_ClaimPaid remained empty, and, therefore, the OMH file was empty too.

Solution

  1. Open the script component and identify all the fields involved in the process.
  2. Run the script on the CUSTOM database and try to understand which of NULL fields causes the issue. In the given situation it was InvoiceStrip field in one of the records: 

select cp.*, p.payee , Replace(Replace(Replace(Replace (Address1, char(13),' '), char(9),''), char(10),''),'    ','') as address1,  p.address2, p.city, p.province, p.postalcode, p.MatchStatus from OMFH_ClaimPaid cp

Inner Join vw_OMFH_Payee p on cp.ClientID = p.ClientID

and cp.ClaimID = p.ClaimID and cp.MailTo = p.MailTo

where cp.clientid not in (

       select ClientID from OMFH_ErrorLog where day(CreDate)=day(getdate()) and month(credate)=month(getdate()) and year(credate)=year(getdate())

       )

and 

(

       p.ClientID is null

       or

       p.ClaimID  is null

       or

       p.MailTo  is null

       or

       cp.ClaimDate  is null

       or

       p.Payee  is null

       or

       p.Address1  is null

       or

       p.Address2  is null

       or

       p.Province  is null

       or

       p.City  is null

       or

       p.PostalCode  is null

       or

       cp.InvoiceStrip    is null

       or

        cp.InvoiceMeter    is null

        or 

        cp.InvoiceLancet    is null

        or

        cp.InvoiceGlucovox  is null

        or

        cp.Strip   is null

        or 

        cp.Meter   is null

        or 

        cp.Lancet   is null

        or

        cp.Glucovox   is null

)

    3. Update the corrupted value.

   4. Re-run Calculate Payable Amount (part 1b) SSIS package from the Visual Studio

   5. Re-Run Unit4Integration - Inbound (Step 2) job.