Wednesday, 28 March 2012

Start and stop BizTalk EDI batches in large scale

If you have been working with BizTalk and EDI Batches you would most likely have run in to problems with starting and stopping EDI Batches as this require you to go into a screen on the party to start and stop the EDI batch:

If you have a lot of EDI parties with batches on them then this is a big job to do if you need to import bindings as EDI batches must not be running when you import bindings with party information. There is an unsupported way around this.

When you press the start or the stop button you are sending a row into the PAM_CONTROL table in the BizTalk management database. And the BizTalk EDI Application have a receive location that looks for data in the table (through a stored procedure). So with a little bit of SQL magic we can stop every EDI batch that is running in the BizTalk Server:


--- Stop EDI Batches
INSERT INTO [BizTalkMgmtDb].[dbo].[PAM_Control]
           ([DestinationParty]
           ,[EdiMessageType]
           ,[ActionType]
           ,[ActionDateTime]
           ,[UsedOnce]
           ,[BatchId]
           ,[BatchName]
           ,[DestinationPartyName])
SELECT edi.PartyId as 'DestinationParty'
      ,[EdiMessageType]
      ,'EdiBatchTerminate' as 'ActionType' --EdiBatchActivate
      ,GetDate() as 'ActionDateTime'
      ,0 as 'UsedOnce'
      ,edi.[BatchId]
      ,[BatchName]
      ,p.nvcName as 'DestinationPartyName'
  FROM EdiPartnerBatchSchedule edi inner join bts_party p
  on edi.PartyId = p.nID
  inner join PAM_Batching_Log pam on edi.PartyId = pam.PartyId and edi.BatchId = pam.BatchId

And if we want to start the batches again we can use this SQL magic (you have to specify which parties that you want enable batches for or some other criteria in the where section):


  --- Start EDI Batches
INSERT INTO [BizTalkMgmtDb].[dbo].[PAM_Control]
           ([DestinationParty]
           ,[EdiMessageType]
           ,[ActionType]
           ,[ActionDateTime]
           ,[UsedOnce]
           ,[BatchId]
           ,[BatchName]
           ,[DestinationPartyName])
SELECT edi.PartyId as 'DestinationParty'
      ,[EdiMessageType]
      ,'EdiBatchActivate' as 'ActionType'
      ,GetDate() as 'ActionDateTime'
      ,0 as 'UsedOnce'
      ,edi.[BatchId]
      ,[BatchName]
      ,p.nvcName as 'DestinationPartyName'
  FROM EdiPartnerBatchSchedule edi inner join bts_party p
  on edi.PartyId = p.nID
where edi.PartyId in (4,20)

A lot easier than going through the parties UI. I have only tested this on BizTalk 2009. I will update when I get a chance to test it on other BizTalk versions.

Random posts: