1

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!

0

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.

  • Makes sense! That was exactly what I thought of, might be happening! Making them work in sequence after one another is the simplest way to set the path right! This has been an issue for a long, long time before even I moved into this role. We are uncovering stuff as we delve deeper into the system. Surprisingly enough, this problem hasn't surfaced at least in the past 3-4 months. Maybe we were just lucky and this was a recipe for disaster already Thanks for your suggestion! – Krishna Mohan May 16 '18 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.