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.