This is some useful information about how EDI Control Numbers are managed within BizTalk 2010.
I have a situation at a client because we were keeping the Party/Agreement nodes in the Bindings file when we promoted new bindings from QA to Production. This was causing the Party Agreements to get dropped and re-created, which reset the control numbers back to 1. The result was 997 rejects for duplicate control numbers.
Moving forward, I will have to be careful to remove the Trading Partners from the Bindings file which we do not want to be dropped and re-created, that way the EDI Control Numbers will be maintained.
This is the answer to my question about the issue from the MSDN Forum link above:
To get the current ISA Control number, you can find it in the BizTalkMsgBoxDb database EdiControlNumbers table. Control numbers are maintained based on the Agreement set up between trading partners. Agreement details can be found in BizTalkMgmtDb – tpm.OnewayAgreement table.
When you import binding file with a party information, which already exists, existing agreement id will be removed and new agreement id will be created with new control numbers(1 as starting value).
Here are the queries I used to help track down the Party to get the OneWayAgreement.Id to use when querying the EdiControlNumbers table.
select * from tpm.OnewayAgreement o
inner join tpm.BusinessIdentity bi on bi.Id = o.ReceiverId
inner join tpm.BusinessProfile bp on bp.ProfileId = bi.ProfileId
where bp.Name like ‘%[name]%’
select * from EdiControlNumbers
where OnewayAgreementId = [id]