Jun 302008
 

This is now the second time that it has happened, and I didn’t write about it before.

I am setting BizTalk up in a multi server environment where the SQL Server exists on a different machine.

I got the following error after applying the configuration: System.EnterpriseServices.TransactionProxyException

After a couple of hours of going thru database permissions, checking MSTDC settings in component services. I found that on the SQL Server MS DTC was not installed (Add/Remove windows components -> Application Server)

DTC

Jun 192008
 

I don’t know how many times I’ve had to execute a stored procedure or a SQL statement from within a BizTalk map or within a C# class, so I’m putting this entry as a reminder to myself of how to do it.

// 1. Create your SQL Connection SqlConnection conn = null; // 2. Create and open a connection object conn = new SqlConnection("Connection String Goes Here"); // 3. Open the Connection conn.Open(); // 4. Create the SQL Command and assign it it a string string strSQLCommand = "SELECT * FROM TABLE"; // 5. Execute the SQL Command SqlCommand command = new SqlCommand(strSQLCommand, conn); // 6. Use ExecuteScalar() to return the first result string returnvalue = (string)command.ExecuteScalar(); // 7. Close the Connection conn.Close(); // 8. Return the Value return returnvalue;

Jun 192008
 

To follow up on why this %BTSbm% variable did not resolve, it needs to be called at the beginning of the script, not after it starts going through it’s determination logic.

Continuing with my entry piece on how to use scripts in your MSI, I have come across instances where the variables are not always available at the various execution stage that I ‘wanted’ them to be.

How I normally install a BizTalk MSI, is right click on the Administration Console and install the MSI, and then afterward is install the application.

After testing the various stages, I have created a table that explains when various variables are available. This greatly helps me put the required testing in the right place in the script.

 

Import without Overwrite Flag

Pre Process

Post Process

BTAD_ChangeRequestAction=Create
BTAD_InstallMode=Import
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_Server={Server Name}
BTAD_Database={BizTalk Mgmt Db}
BTAD_ChangeRequestAction=Create
BTAD_InstallMode=Import
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_Server={Server Name}
BTAD_Database={BizTalk Mgmt Db}

Import with Overwrite Flag

Pre Process

Post Process

BTAD_ChangeRequestAction=Update
BTAD_InstallMode=Import
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_Server={Server Name}
BTAD_Database={BizTalk Mgmt Db}
BTAD_ChangeRequestAction=Update
BTAD_InstallMode=Import
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_Server={Server Name}
BTAD_Database={BizTalk Mgmt Db}

Install

Pre Process

Post Process

BTAD_ChangeRequestAction=Update
BTAD_InstallMode=Install
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_ChangeRequestAction=Update
BTAD_InstallMode=Install
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5

Uninstall

Post Process

Pre Process

BTAD_ChangeRequestAction=Delete
BTAD_InstallMode=Uninstall
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5
BTAD_ChangeRequestAction=Delete
BTAD_InstallMode=Uninstall
BTAD_InstallDir={Installed Directory}
BTAD_ApplicationName={Application Name}
BTAD_SilentMode=5

 

My exact situation was in the Post Process script I needed to access the server name, but it was not resolving %BTAD_Server% because I had it filtering to only run during the Install process, not the Import process.

The script used to look like this:

@setlocal

set LogFile=”c:\BTAD_ApplicationName%_PostProcess.log”

set BAMLog=”c:\BTAD_ApplicationName%_BAM.log”

if “%BTAD_ChangeRequestAction%”==”Update” (

   if “%BTAD_InstallMode%”==”Install” (

      if “%BTAD_HostClass%”==”BizTalkHostInstance” (

          net start “BizTalk Service BizTalk Group : BizTalkServerApplication” >> %LogFile%

         sqlcmd -S %BTAD_Server%\BAMPrimaryImport -i “%BTAD_InstallDir%\enhanceBAM.sql” -o %BAMLog%

      )

   )

)

But because the BTAD_Server is not available at this stage, it needed to be changed to

@setlocal

set LogFile=”c:\BTAD_ApplicationName%_PostProcess.log”

set BAMLog=”c:\BTAD_ApplicationName%_BAM.log”

if “%BTAD_ChangeRequestAction%”==”Update” (

   if “%BTAD_InstallMode%”==”Import” (

      if “%BTAD_HostClass%”==”BizTalkHostInstance” (

          net start “BizTalk Service BizTalk Group : BizTalkServerApplication” >> %LogFile%

         sqlcmd -S %BTAD_Server%\BAMPrimaryImport -i “%BTAD_InstallDir%\enhanceBAM.sql” -o %BAMLog%

      )

   )

)

Jun 192008
 

For those of you who would like to know what role links are and how you can use them in everyday life, I have a very simple customer request, and an even simpler prototype to demonstrate how to implement role links.

My situation is that I process EDI (HIPAA) files, and there is a standard map that translates the file to a flat file structure. However, the output needs to go into a client specific folder for the existing process to start.

Looking at the source of all truth and knowledge here:

A role is a collection of port types that either uses a service or implements a service. A role defines how parties interact with orchestrations. For example, an orchestration might use the role of Shipper. The Shipper has one or two parties associated with it. When the orchestration decides which shipping company to use to ship an item, it compares the prices of the parties in the Shipper role.

With my limited knowledge of Role Links, I thought that this might be a way to have one orchestration tied to an variable list of send ports. I figured that if I was a Shipper, I want to decide which shipping company (in my case two different folders) and then send it off to them.

After looking at this site and this site I was still very lost as to how to implement it. So I started playing around, and decided on a very simple prototype:

  • Receive a file
  • Interrogate the contents of the file for who the sender is
  • Set the Role Link
  • Send it out with some type of indicator so I know which sender it was

Pretty easy I thought, and actually implementing it was not too hard.

  1. I created an empty BizTalk project
  2. I constructed a sample file and since I am lazy, I made Data a distinguished field. 
  3. I then created an orchestration that had a Port Type defined from the schema I just created.
  4. I then created a Recieve Port using the FilePortType.
  5. I then created a new Role Link Type and named it SendingRoleLinkType
  6. When you expand the new link type, and right click on Role_1, it asks you to Add Port Type
  7. So of course I do, and I choose the existing port type, in the Port Type Wizard
  8. I also rename the Role_1 to Sender
  9. I then created a role named Destination
  10. I am using an existing Role Link Type already defined
  11. It is the Consumer role.
  12. I created two parties and for the HIPAA accelerator, the format is EDI:\\ISASenderID:Qualifier:GSSenderID
  13. These parties are defined as Party1 (EDI:\\123456789:ZZ:123456789) and Party2 (EDI:\\987654321:ZZ:9876543521) CORRECTION: the parties should have been defined as EDI://123456789:ZZ:123456789 and EDI://987654321:ZZ:9876543521 for them to be correct

  14. I then created an expression after my receive shape to assign the role link, and here is the code:

      Destination(Microsoft.XLANGs.BaseTypes.DestinationParty)=
      new Microsoft.XLANGs.BaseTypes.Party(@”EDI:\\”+FileMsg.Data+””,”EDI”);

      Notice that in the argument 1 of Party, is the Value defined in the Party Definition, and obscurely, the OrganizationName (EDI) is the value it is looking up against. I am pulling the partner information (FileMsg.Data) out of the file (which we will see later).

  15. I then created a send shape and connected it to the Destination Port Link
  16. I deployed the project and bound the input to a default xml pipeline
  17. Now the part that exposes the power of Role Links I created a send port for trading partner 1 called, oddly enough, Party 1 Destination and set the path to E:\Role Link\Party1\%SourceFileName%.%MessageID%.xml, I did the same for trading partner 2, called Party 2 Destination and set the path to E:\Role Link\Party2\%SourceFileName%.%MessageID%.xml

  18. In the Party definition I clicked on the send port and chose the correct send port:

  19. Now we need to ‘bind’ the sending to the correct variable port, how you do that is if you expand the Roles, you will see the Sender Role
  20. Right click this and Enlist the Party, Enlist Party1 Then you come up with a Elist Party Properties dialog where you choose which of the ports defined in step 18 to use:

  21. After that is completed, you can enlist the orchestration and start throwing files, oh, here is a sample input to show you the data it was pulling:

Things to take away from this little tutorial:

Role Links allow you to create a lookup feature within an Orchestration to determine where to send the data, and still have all of the information available to be modified through the administration console.

The example here is for one business process, in my immediate need, for eligibility files, but later we are going to need to have a different location for claim files, while the party definition cannot change, I need to create new send ports, and then add them to the list of send ports in the party definition, and then when enlisting the role, I choose the newly created port! All VERY useful.

Jun 182008
 

I finally was able to start working with BizTalk R2, and we do HIPAA/EDI. Within hours of ‘playing’ with it, I was SHOCKED! How did this get out the door?! Who signed off saying this solves anyone’s EDI integration problems?

Okay — so you are right — it allows the EDI shop to be able to handle eleventeen million different transactions out of the box, yes that is cool, and actually quite useful.

However, the part that keeps me up at night is not: “how am I going to translate this transaction?” but “what happened to this file yesterday, or the other file two weeks ago?”

I had gone to plenty of MS conferences where during the beta bits were shown and the reporting was going to be done using BAM, “cool” I thought, if MS is going down the BAM trail, I should too, so my whole paradigm of reporting changed and now I am an even bigger proponent of BAM.

Once I started playing with the R2 bits, maybe hour two, I was scratching my head, where was all of this famed reporting?

Let me not bore you too much with what R2 has for reporting, I will bullet list the things I required that are not present in the current reporting architecture

Error information in a repository that associated the file with the error

I am not a big fan of reading through the event log to try to find the message that failed and then equally tasking process of finding the event log entry that actually tells me what the error actually is. Even MS was not correct when they stated that the BizTalk 2006 Server message id error and the corresponding BizTalk Server 2006 EDI error are paired together:

ediErrors

 

Being able to associate the TA1/997 to the original message.

Yes, I know that there is a column in the BAM tables to associate it, but it is not used! The only suggestion was to put a warm and fuzzy query that pulls the control number/sender id/receiver id to associate the original message to the corresponding acknowledgment. This did not work for me, as I test like crazy, throwing the same file at a process eleventeen million times a time before I a satisfied, and there isn’t a great way to pair up the values programmatically.

AckCorrelationId

interchangeAckId

 

Finding the original filename.

I know that in the perfect world, we don’t care about file names, we simply care about sender id/control numbers, etc: but our trading partners are in love with filename, bordering on creepy! When they call up the first thing that they say is “I dropped of ‘ABCDEFG12345.edi’ last Wednesday – I never got an ACK, what happened?” I have never gotten, without pulling out my crowbar; what their id is, and the control number.

Ability to see the entire EDI on both the receive and send side.

The transaction (that is stored deep in the DTADb database) doesn’t help me if I have a hard time associating it with it’s entire interchange.

 

What I did:

I wrote a little email to Steve Ballmer that got some people calling me to assist on an issue where if I wanted to reject an entire EDI interchange if any transaction was in error. It was/still is pretty hard to get a non documented feature limitation corrected. I decided that I had fought my battles and was not going to do it again, so I wrote some of my own components:

MY NEW EDI REPORTING

I have a separate datastore that is located on the same database as the BAMPrimaryImport (yes I know I shouldn’t) it is SQL 2005 so I can store quite a large amount of data in the following table:

ediRepository

The other things I wanted to log was all of the context properties that are part of the message (and some that aren’t). Here is a snapshot of the view that takes all that will need to know when a trading partner calls up:

ediData

Some notes, I have been testing with 50mb – 100mb files and the view was coming up pretty slowly, so I limited what is shown  in the last column is limited in the view to 2500 bytes, and added the Archive Interchange Id so if you need to look at the whole interchange you can run a query against the EDI_Repository.

To get all of this data to show up, I created a couple of pipeline components:

Receive Pipeline

receiveEDIPipeline

Which you have the following pipeline properties to set when you define your receive location:

receivePipelineConfig

And for our outbound EDI data:

Send Pipeline

sendPipeline

Which you have the following pipeline properties to set when you define your send port:

sendPipelineConfig

You have should specify the database and server, if it can’t find one that is associated with them message (if it wasn’t already attached from the receive side (an automatically generated 997/TA1 for example)) then it will put it in the database you specify.

This is only geared to ANSI X12 transactions, including HIPAA transactions, I have not tested it for UN/EDIFACT.

If you are interested in having this, refer to the services page.