Sep 262007

We are implementing a new solution for a client that requires that we connect to an FTP site to gather their data. This is normal stuff. The challenge came when the requirement was brought to our attention that the FTP password was going to be changed every 30 days. We did not want to have to coordinate when the password was going to change and who was going to be doing it. We needed a way to programmatically change the password of the FTP receive location so we did not have to baby sit the receive location.

Taking the SDK as our base, we developed some code that interrogated the ReceivePort, its RecieveLocations, and then once found it, would modify the IReceiveLocation.TransportTypeData.

What started off as a simple request to change the password ballooned to many other properties of the FTP adapter. I was happily coding until I got to the URI, and realized that it had a bunch of downstream affect, where I then gave up. The following code only deals with a few of the properties that can be changed in the FTP adapter, but it gives you a starting point to develop more code against.

This code assumes a few things; you accept the disclaimer to the right and that you already know which port and which receive location you want to change, and it will go in an change the particular property in the adapter.

We also had the requirement to change a pipeline component password (it picks up a zipped file that has a password, so the same logic can be used, and instead of modifying location.TransportTypeData, you simply modified IReceiveLocation.ReceivePipelineData.

using System; using System.Collections.Generic; using System.Text; using Microsoft.BizTalk.ExplorerOM; namespace ChangeConfigration { public enum Properties { Password, UserName, RepresentationType, MaximumNumberOfFiles, PassiveMode, FirewallType, FirewallPort, PollingUnitOfMeasure, pollingInterval, ErrorThreshold, MaxFileSize } public class ReceiveLocations { static void ChangeExistingFTPLocation(string ReceivePort, string ReceiveLocation, Properties PropertyType, string NewProperty) { string thisPropertyType; switch ((int)PropertyType) { case 0:thisPropertyType = "password"; break; case 1:thisPropertyType = "userName"; break; case 2:thisPropertyType = "representationType"; break; case 3: thisPropertyType = "maximumNumberOfFiles"; break; case 4: thisPropertyType = "passiveMode"; break; case 5: thisPropertyType = "firewallType"; break; case 6: thisPropertyType = "firewallPort"; break; case 7: thisPropertyType = "pollingUnitOfMeasure"; break; case 8: thisPropertyType = "pollingInterval"; break; case 9: thisPropertyType = "errorThreshold"; break; case 10: thisPropertyType = "maxFileSize"; break; default: thisPropertyType = "unknown"; break; } BtsCatalogExplorer root = new BtsCatalogExplorer(); try { root.ConnectionString = "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;"; //Enumerate the receive locations in each of the receive ports. foreach (ReceivePort receivePort in root.ReceivePorts) { if (receivePort.Name == ReceivePort) { foreach (ReceiveLocation location in receivePort.ReceiveLocations) if (location.Name == ReceiveLocation) { string OldTransportTypeData = location.TransportTypeData; int start = OldTransportTypeData.IndexOf("<" + thisPropertyType + ">") + Convert.ToString(thisPropertyType).Length + 8; int end = OldTransportTypeData.IndexOf("</" + thisPropertyType + ">"); int length = end - start; string ExistingProperty = OldTransportTypeData.Substring(start, length); string NewTransportTypeData = OldTransportTypeData.Replace( "<" + thisPropertyType + ">" + ExistingProperty + "</" + thisPropertyType + ">", "<" + thisPropertyType + ">" + NewProperty + "</" + thisPropertyType + ">"); location.TransportTypeData = NewTransportTypeData; location.Enable = true; } } } root.SaveChanges(); } catch (Exception e)//If it fails, roll-back all changes. { throw e; root.DiscardChanges(); } } } }

This goes without saying that we picked up the file and then waited for a given amount of time and then changed the password for the ftp receive location and logged what that password is in BAM so that if we ever needed to find out what the password is, we could look at the BAM view.

Yes, I know I have the possibility of getting hate mail by stating that I am going to log the password as a clear text value in BAM, however, using the IReceiveLocation Interface API, I can see what the password is in clear text anyway. I might as well make it easy for the support staff to log into an internal web site and see what the password currently is so they can go out to the FTP site and see if there are files outstanding than having to call up the client to find out what the password is, or having to call the BizTalk team to have them have a program that goes in and pulls the data and sees what the password is and tells them what it is. (whew, I almost ran out of breath with that run-on sentence!)

Sep 242007

I was given the requirement to pull information from a Sybase Database to cross reference and ID.  I’ve had a similar requirement in pulling data from a SQL stored procedure, so this was pretty similar.


First I used the Sybase.Data.AseClient.dll and the sybdrvado11.dll provided by Sybase as part of their client install.  Both of these .dll’s need to be installed into your ..\Program Files\Microsoft BizTalk Server 2006\  folder.


I created a VB.NET class that contains the function that brings in the ID I needed to cross reference and the connection string to the Sybase database.


The connection string is in the format Data Source=<IP Address>;Port=<Port Number>;Database=<Database Name>;UID=<UserName>;PWD=<Password>;Max Pool Size=50;Min Pool Size=5″


Here is the VB.NET class with accompanying comments.

Imports System.Data Imports Sybase.Data.AseClient Imports System.Xml Public Class DatabaseConnectionClass Public Function ReturnNEWID(ByVal OLD_ID As String, ByVal ConnectionString As String) As String 'Create Connection Dim conn As New AseConnection(ConnectionString) 'Open a Connection to the Database conn.Open() 'Create the SQL Statement Dim sql As String = "SELECT Field from TABLE WHERE ID= '" + OLD_ID+ "'" 'Retrieve the Data Dim cmd As New AseCommand(sql, conn) 'Return the Data Reader Object Dim reader As AseDataReader = cmd.ExecuteReader() 'Populate the Return Value While reader.Read() Return reader.GetString(0) End While 'Close the DataReader and Connection objects reader.Close() conn.Close() End Function End Class

I signed the class with a strongly named key, built and GAC the class, and referenced it as an external assembly.



When I test the map it correctly pulls from the Sybase database.

Sep 242007

When running the configuration wizard for the HIPAA accelerator, I have come across an occurrence where I get the following error:

Error 1

In looking at the log you will see the following enties

Executed SQL statement on database: BizTalkMgmtDb on computer: {Server}
Executed SQL statement on database: BizTalkMgmtDb on computer: {Server}
User, group, or role ‘{USERID}’ already exists in the current database.
SQL error: 42000 Native error code: 15023
c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(1747): FAILED hr = 80040e14

c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(1955): FAILED hr = 80040e14

c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(1365): FAILED hr = 80040e14

c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(720): FAILED hr = 80040e14

Failed to execute SQL statement on database: BizTalkMgmtDb on computer: {Server}
User, group, or role ‘{User}’ already exists in the current database.
c:\depots\depothipaav33\private\source\hipaaconfig\dll\hipaaconfig.cpp(2179): FAILED hr = 80040e14

The solution is to go into the database and for the property that is running the service uncheck Management database

Existing Property


New Property

Sep 212007

Have you ever received the error “(80) The document is a duplicate. Contact the sender.” in the Biztalk HIPAA Adapter?.

I’ve only seen it 1.35 million times.  Help is on it’s way.

After many years of manually changing ISA/IEA control numbers for the BizTalk HIPAA adapter, I decided I would get some help via VB.NET code.  Here is a little application that will generate new HIPAA EDI files from an entire folder with the File Mask you specify with all new ISA/IEA control numbers.




If you’d like it contact me:

Sep 202007

Stephen Thomas hit me up to write an article for his newsletter (if you aren’t automatically getting it, you should sign up for it here).

I wrote about how to automatically uninstall a BAM definition as part of the MSI uninstall. You can read about it here:

The one dilemma I have is that I cannot seem to figure out how to get a batch script to resolve the installation path by using this code in a batch script:

for /f “skip=2 tokens=2,*” %%a in (‘reg query “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0” /v “InstallPath”‘) do SET BTSbm=”%%~bTracking\bm.exe remove-all -DefinitionFile:”

I am looking into it and will update this blog entry when I finally do figure it out.

Sep 192007

With the 2006 deployment paradigm shift, I have been kind of left behind.

I have lately been thinking a lot about how to make things better/easier/lazier for me, and it got me looking at the new automatic features in the creation of the MSI.

One of the things is the ability to deploy BAM definitions automatically. Let’s review how this is done:

  1. On the development server, you still need to manually deploy the BAM definition.
    bm deploy-all
  2. You then want to add the BAM definition file to your application as a resource. Some things to keep in mind, you need to make sure you define a destination, otherwise the BAM definition file does not exist on your next environment. I am using variables that are accessible from the BTSTask.
    BAM Resources
    Doing this will ensure a successful installation of your BAM definition on your new environment, congratulations! However the issue is that if you undeploy the BizTalk application, the BAM definition still exists and is not removed. I asked Keith Lim if this was a feature or flaw. He informed me that this was by design. “I checked with our feature team and this is indeed by design.  The reason is that, for BAM activity deployment, there could already be user data in the database tables and we don’t want to risk having user data deleted implicitly.” — Well, for me, most times I don’t want to have to manually go to an environment that I normally don’t have access to anyway and undeploy BAM definitions if I need to redeploy code, because 90% of the time I will want to start all over with BAM than keep working with the old data. I want a way to automatically undeploy the BAM definition! Here is how:
  3. Now you will need to add a script that will actually remove the BAM Definition. Let’s first create the script (uninstall.bat) and then add it. The code below does the following, determines that it is undeploying and then undeploys the BAM definition
    set LogFile=c:\%BTAD_ApplicationName%_Undeploy.log
    REM ### Uninstall part of the script called for an existing application
    if “%BTAD_ChangeRequestAction%”==”Delete” (
      if “%BTAD_InstallMode%”==”Uninstall” (
        if “%BTAD_HostClass%”==”BizTalkHostInstance” (
            echo “c:\Program Files\Microsoft BizTalk Server 2006\Tracking\bm.exe” remove-all -DefinitionFile:”%BTAD_InstallDir%\%1″ >> “%LogFile%”
            “c:\Program Files\Microsoft BizTalk Server 2006\Tracking\bm.exe” remove-all -DefinitionFile:”%BTAD_InstallDir%\%1″ >> “%LogFile%”
            echo “Pre uninstall part of the script called for %BTAD_ApplicationName%” >> “%LogFile%”

    Now you might ask yourself, “why don’t you determine the path of the install directory right from within the batch file” like this:

    for /f “skip=2 tokens=2,*” %%f in (‘reg query “HKLM\SOFTWARE\Microsoft\BizTalk Server\3.0” /v “InstallPath”‘) do SET BTSbm=”%%~gTracking\bm.exe remove-all -DefinitionFile:”
    echo “%BTSbm%%BTAD_InstallDir%\%1” >> “%LogFile%”

    I tried for quite a while, but for some reason the logic will not resolve to the install path. If anyone knows why, I would be happy to add how to get it to work, in the meantime, go ahead and hard code the install path.

  4. Now you would think that you would simply add the batch file as a PostProcessingScript like this
    however there is no way to add an argument to the calling of this batch file (remember “%BTAD_InstallDir%\%1” ?) So we need to manually add it. We also need to make sure it is a PostProcessingScript, as the documentation states here: “During uninstallation, all scripts run in the opposite order that they run during installation. Therefore, post-processing scripts run at the beginning of uninstallation and pre-processing scripts at the end of uninstallation.” – We don’t want to delete the BAMDefinition.xml before the undeploy process gets to it do you?
  5. I need to add the batch script with an argument (the BAMDefinition.xml). I need to follow the instructions as documented here.
      Import PostProcessing Script
  6. Let’s go back and see the script has been added correctly (click to enlarge)
  7. Let’s create the MSI and don’t extract the Global Parties (since we don’t care about them anyway)
     Create MSI Part 1
  8. Save the file
     Create MSI Part 4
  9. Now you can install it in your new environment. Having done that, just to make sure, lets take a look at the install directory to make sure the two files are there:
     Installed Files
  10. Also the BAM Views are there:
    BAM Views
  11. So lets undeploy the code and see if the magic happens! (Of course it would, or this blog entry would be a total sham!)
  12. And finally the log that we created in the batch script

Congratulations, you now have a MSI that will deploy a BAM solution to the environment you need and will also undeploy the definition when you uninstall the application!


Wouldn’t it be nice to have a dialog box during the uninstallation of a BizTalk application where there is a BAM component asking if we want to undeploy instead of these hoops?

My personal lessons learned:

  • Don’t have spaces in the application name or the BAM Definition file: the BTSTask variables behave oddly and when passing variables into the batch file is almost impossible to troubleshoot
  • Even though I could run a batch file manually that would resolve the installation path of BizTalk, the resolving always came back as null
  • If it is a PreProcessingScript, it will delete the folder in the Program Files directory (and all subsequent files stored there) before executing the script
  • Even though it warns “You should always write scripts intended for production systems in silent mode. This is because a script waiting for user input will cause the BizTalk databases to become locked and inaccessible until the input is received.” I had to test it, I put a pause in the batch file and I ended up rebooting the machine because the installation script was waiting for me to press a key on a DOS prompt that wasn’t available!
Sep 072007

If you compile a flat file disassemble pipeline with a schema as is required in the note and then remove the schema in the management console as shown here:


and changed it to


You will get the following error (Value does fall within the expected range) in the event log when trying to process a file:


Sep 042007

Microsoft’s documentation team has given you (those registered with MSDN online) the ability to add your own content to MSDN.

Here is your chance to tell the world of your knowledge in a bigger venue than a place like blogs (that most likely are not as frequented as much as MSDN).

Some things to keep in mind:

Code of Conduct

MSDN Wiki Frequently Asked Questions

Contribution Agreement

An example is here: