Sql-server – Parallel updates on header and line item tables in SSIS

sql server

This is my scenario

  1. I have 2 tables – a header and a line item table which join on 2 columns contract number and contract version

  2. 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()

  3. 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()

  4. Both these queries are put in a Container task in SSIS, and are unconnected data flow tasks as shown in the below image

Image

  1. 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

  2. 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!

Best Answer

Less of a race condition and more of an issue with order of operations and dependencies.
The way you stated your question, in steps, is how you want to think of the SSIS package. First it has to do this, then it has to do that.
Since the line items are dependent on the latest information from the header, that should always come second. Letting them go simultaneously, you don't really know what happens first.

Related Question