This is my scenario
- I have 2 tables - a header and a line item table which join on 2 columns contract number and contract version
First, I update the header table to set the status code as 'T' for whatever records have an expiration date in the past on the header table. The query looks like this:
Update Contract_Header set status_code = 'T' where status_code = 'A' and expiration_date < getDate()
Then, I update the line item table to set status code as 'T' for all active line items having an expiration date in the past, and are corresponding to those contract numbers and versions in the HEADER table with a status code of 'A' (The logic might sound a bit crazy but that's how it was designed in the first place)
Update C set C.Status_Code='T' FROM Contract_Line_items C JOIN Contract_Header H ON C.contract_nbr = H.contract_nbr AND C.contract_version = H.Contract_Version WHERE H.status_code='A' AND C.status_code='A' AND C.expiration_date is not null AND C.expiration_date < getDate()
Both these queries are put in a Container task in SSIS, and are unconnected data flow tasks as shown in the below image
Technically, only one of those tables - either the header or the line item can be set to 'T' for a contract number and version combination, because by the time the line item table gets updated, the header status for that record would already have been set to 'T'. But, due to some reason, last week, both of them were set to 'T' for the same contract number, version combinations. In the subsequent execution, the issue got resolved by itself and only the header was set to 'T', and not the line items
We think this is some sort of a race condition, but would linking the tasks to execute after each other help the purpose? Is this really a candidate for a race condition? Any other suggestions are also appreciated
Thank You!