How to retrieve EDI Partner Information for context based EDI routing Part 1

 
Based on Sender and Receiver Party Names
 
Retrieve ISA SEGMENTS for EDI Context Based routing.
Something we needed for EDIGUIDANCE.   
 
 
NOTE: THIS WILL BE CHANGING AS I LEARN TO SEPERATE OUT THE EDI FROM EDIFACT.  IF YOU HAVE NO EDIFACT THIS SHOULD WORK FINE.
 
I dug around the databases after creating a party.
 
 
How to grab the AS2 and EDI based on Party to and From Names

STORED PROC : For Single X12 and AS2 Agreements from Party->Party

— USE

[BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:07:29 ******/

SET

ANSI_NULLS ON

GO

SET

QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]

(

@Sender

varchar(255),

@Receiver

varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

IsNull(agreement.Name,) as ‘EDIHEADER!1!AgreementName’,

IsNull(sender.Name,) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,) as ‘EDIHEADER!1!ReceiverName’,

IsNull(businessIdentitySender.Qualifier,) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,) as ‘EDIHEADER!1!ISA6’,

IsNull(businessIdentityReceiver.Qualifier,) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,) as ‘EDIHEADER!1!ISA8’,

IsNull(AS2.AS2From,) as ‘EDIHEADER!1!AS2From’, IsNull(AS2.AS2To,) as ‘EDIHEADER!1!AS2To’

from

[BizTalkMgmtDb]

.[tpm].[Partner] sender inner join

[BizTalkMgmtDb]

.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb]

.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId left outer join

(

select

businessIdentitySender

.Value as AS2From,

businessIdentityReceiver

.Value as AS2To,

sender

.Name as SenderName, receiver.Name as ReceiverName

from

[BizTalkMgmtDb]

.[tpm].[Partner] sender inner join

[BizTalkMgmtDb]

.[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb]

.[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb]

.[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender

.Name = @Sender and receiver.Name = @Receiver and

agreement

.Protocol = ‘as2’ and

businessIdentityReceiver

.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’

) as AS2 on AS2.SenderName = sender.Name and AS2.ReceiverName = receiver.Name

WHERE

sender

.Name = @Sender and receiver.Name = @Receiver and

agreement

.Protocol = ‘x12’

and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’

FOR XML EXPLICIT

END

GO

__________________________________

The Test:

— USE

[BizTalkMgmtDb]

GO

DECLARE

@return_value int

EXEC

@return_value = [dbo].[GetAS2AndEDIInformationBySenderAndReceiverName]

@Sender = N’ACME CORPORATION’,

@Receiver

= N’EDIGUIDANCE’

SELECT

‘Return Value’ = @return_value

GO

OUTPUT

<EDIHEADER AgreementName="Acme To EDIGUIDANCE" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" ISA5="ZZ" ISA6="ACME" ISA7="ZZ" ISA8="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

 

STORED PROC : For Multiple X12 Agreements from Party->Party

— USE [BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetEDIInformationBySenderAndReceiverName] Script Date: 09/18/2010 01:51:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetEDIInformationBySenderAndReceiverName]

(

@Sender varchar(255),

@Receiver varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

IsNull(agreement.Name,) as ‘EDIHEADER!1!AgreementName’,

IsNull(sender.Name,) as ‘EDIHEADER!1!SenderName’, IsNull(receiver.Name,) as ‘EDIHEADER!1!ReceiverName’,

IsNull(businessIdentitySender.Qualifier,) as ‘EDIHEADER!1!ISA5’,IsNull(businessIdentitySender.Value,) as ‘EDIHEADER!1!ISA6’,

IsNull(businessIdentityReceiver.Qualifier,) as ‘EDIHEADER!1!ISA7’, IsNull(businessIdentityReceiver.Value,) as ‘EDIHEADER!1!ISA8’

from

[BizTalkMgmtDb].[tpm].[Partner] sender inner join

[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender.Name = @Sender and receiver.Name = @Receiver and

agreement.Protocol = ‘x12’

and businessIdentityReceiver.Qualifier <> ‘AS2Identity’ and businessIdentitySender.Qualifier <>‘AS2Identity’

FOR XML EXPLICIT

END

GO

OUTPUT

<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

STORED PROC : For Multiple AS2 Agreements from Party->Party based on party names

 

— USE [BizTalkMgmtDb]

GO

/****** Object: StoredProcedure [dbo].[GetAS2InformationBySenderAndReceiverName] Script Date: 09/18/2010 01:52:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author: <Mark Rowe>

— Create date: <September 17, 2010>

— Description: <EDIGUIDANCE Addin>

— =============================================

CREATE PROCEDURE [dbo].[GetAS2InformationBySenderAndReceiverName]

(

@Sender varchar(255),

@Receiver varchar(255)

)

AS

BEGIN

select 1 as tag,

null as parent,

agreement.Name ‘EDIHEADER!1!AgreementName’,

sender.Name as ‘EDIHEADER!1!SenderName’, receiver.Name as ‘EDIHEADER!1!ReceiverName’,

businessIdentitySender.Value as ‘EDIHEADER!1!AS2From’,

businessIdentityReceiver.Value as‘EDIHEADER!1!AS2To’

from

[BizTalkMgmtDb].[tpm].[Partner] sender inner join

[BizTalkMgmtDb].[tpm].[Agreement] agreement on sender.PartnerId = agreement.SenderProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentitySender on businessIdentitySender.ProfileId = sender.PartnerId inner join

[BizTalkMgmtDb].[tpm].[Partner] receiver on receiver.PartnerId = agreement.ReceiverProfileId inner join

[BizTalkMgmtDb].[tpm].[BusinessIdentity] businessIdentityReceiver on businessIdentityReceiver.ProfileId = receiver.PartnerId

WHERE

sender.Name = @Sender and receiver.Name = @Receiver and

agreement.Protocol = ‘as2’ and

businessIdentityReceiver.Qualifier = ‘AS2Identity’ and businessIdentitySender.Qualifier = ‘AS2Identity’

FOR XML EXPLICIT

END

GO

OUTPUT

<EDIHEADER AgreementName="ACME to EDIGUIDANCE AS2" SenderName="ACME CORPORATION" ReceiverName="EDIGUIDANCE" AS2From="ACME" AS2To="EDIGUIDANCE" />

 

Share on Facebook

EDIGUIDANCE Team

Developers:
Karl Rissland
Mark Rowe
Doug Marsh
Toya Lofton
 
Advising Experts:
Brad Friedlander
Eric Stott
Ben CLine
Jim Dawson
 
Project location:  http://ediguidance.codeplex.com
 
Share on Facebook

EDI Guidance Meeting

Ms Office in Alpharetta.  Friday September 17th 12:30pm-3pm
Email me for details.
 
Part 1: This will be a share on what problems were encountered in ESB 2.1 including installation EDIGUIDANCE upgrades and going over depreciations since ESB 2.0.
Part 2: Setting specific goals.
 
Share on Facebook

EDIGuidance CONVERSION to 2010Beta and ESB 2.1

 

 

 

 

Fiddled with SNK’s and rebooted re-tried conversion, worked fine. Checking back in now.

 

Share on Facebook

Itineraries and Extenders working.

 
I will be starting back on EDI friday.
 
Share on Facebook

An excellent supplemental guide for ESB 2.0

Share on Facebook

Older Users Group Speech,

Please join us for the first meeting in 2010 of the Georgia Chapter of the BizTalk Server User Group on January 19th.

 

Karl Rissland of Microsoft will be giving a give a quick roadmap presentation and sharing some of the key BizTalk videos from the Microsoft Professional Developers Conference. Karl will also be providing a 15 minute overview concerning the announcement of BizTalk Server 2009 R2.

Mark Rowe of Magenic will be presenting on a “” using the ESB 2.0 Giuidance for 2006R2/2009. Mark’s presentation will go into depth concerning;

1.      How to decide to thrown an error on the decode or let it error out to not allow for the original file to be lost.

2.      Creating a Gentle Exception Wrapper re-throwing that new Exception.

3.      Catching that Gentle Exception

4.      Emulating the ESB FaultMsg.

5.      Creating a “catch all” for all errors within an organization.

6.      Changing the Catch All into Gentle Errors.

7.      Creating a “common” BizTalk error handling Orchestration to take in all of the above errors and properly forward them to wherever they need to go.

.

https://www.clicktoattend.com/invitation.aspx?code=143913

Share on Facebook

Simple AS2 Certificate installation for VANS.

This is when you can’ get it to work, the below should work if followed step by step.
 
*If you are using Active Directory Certificate Services you can TRUST the AD without having to load this locally, this quick setup will work on load balanced BT servers, however this specific instace for TRUSTS is for local single machine installations.
 
*Check that the serial number in the PFX P12 (personal key ) DOES NOT start with a 00.
 
Create one user for the Inprocess and the isolated host adapters, add them to the Biztalk administrators group and local Administrator group.
For this example we will call it BTAS2User.
 
run -> MMC.exe as this user
 
File-> Add/Remove Snapin
and choose Certificates
 
Select the Local Computer account
Select Certificates and Click Add
 
Add the Van’s PUBLIC key (CER) and YOUR Private Key(PFX P12)
 
Add the Private Key to your private store (PFX, P12)
 
Open the  Biztalk Administrator
Expand until you see the BIZTALK GROUP on the local machine [Right-Click] then select Properties
 
Add the new Private Key here.  (PFX, P12)
 
You will see your personal certificates available here.
 
 
I am unsure if this is 100% needed, however we added them as well to both HOSTS
Select the HOSTS folder in teh biztalk administrator (next to the host instances folder) 
 
Then one at a time select each and [Right-Click] -> Properties
Add the Certificates here.
 
It should work if you just restart the host instances, however I rebooted since it was a "pre-production" server .
 
Share on Facebook

AS2 Certificate MDN’s Force response Setup.

Share on Facebook

One work around for Management Portal Issues. ESB 2.1 Part 1 – Access is Denied

SSO AUDIT
 Function: GetConfigInfo (SSOProperties)
 Tracking ID: 02e78f85-cfa0-41fb-83fd-d1077521108f
 Client Computer: IRDEV (w3wp.exe:4128)
 Client User: WORKGROUPIRDEV$
 Application Name: ESB.instrumentationConfiguration
 Error Code: 0x80070005, Access is denied.

I went and tried to open up the main homepage for the ESB 2.1 Management Portal and low and behold it didn’t work. Baring teeth smile

Though up to this point I had been winging it with the guide and search engines, so I had to rethink many of the prevous installation steps. Nerd smile

I found that my application log was litered with 1 single call. Sarcastic smile

Low and behold there were access errors, and after a long day of creating different log ins and trying to keep track of this I knew this wasn’t going to be a 5 minute fix.

 

This was my first attempt

I set the Server and ServerAll to my PC Name

ssomanage -serverall IRDEV

ssomanage -server IRDEV 

 Though, as seen in yesterdays blog I did this yesterday as well, but hey the . was back for my local user which was the user I had used for  many of the configuration settings.

 

At first I thought it may be the user on World wide publishing Service due to the w3wp.exe:4128

Though that wasnt the problem

So  I went into the Application Pools and changed the Application Pool:EsbPortalNetworkAppPool

 

Set that user with proper permission (which I am assuming is an SSO Administrator)

My Main user was an SSO Admin.

This only left me with a "Application does not exist,"  which I covered in the previous post.

Share on Facebook