Eric's BizTalk 2004/2006/2009 blog

We have developed a HIPAA 4010A1 to HIPAA 5010 converter for use in Visual Studio 2008.

After installing the converter, the steps to use the converter are as follows:

1. Place the appropriate 5010 HIPAA schema in the same project as your current 4010 HIPAA schema
   
2. Compile the schema project
3. Open up map project that references the HIPAA schema project
4. Right click on the map that needs to be converted
5. Choose ‘Convert to 5010’

It is complete.

Things the converter will do:

• Create a backup of the current map as {map name}_4010.btm and add it to the project
• Change the type name of the 4010 map to {type_name}.4010

Here are the screen shots of the tool:

After pressing the Convert to 5010 context menu item the project has the following archived map.

Here is a page from the initial map:

Here is the converted map:

Also for an additional fee, there will be 4010A1 to 5010 maps and conversely, 5010 to 4010A1 maps.

Through the contact page, you can pre order this 5010 converter.

I will not be delivering this however, until Microsoft officially releases the HIPAA 5010 schemas.

Here are the steps to configure a dual computer setup where SQL Server and BizTalk are hosted on two separate computers.

Steps to configure SQL Server (just the pictures where not the default settings are chosen)

View Full Album

Steps to configure the BizTalk machine after the SQL Server is installed correctly (just the pictures where not the default settings are chosen)

View Full Album

Perhaps you are restarting a host instance, maybe you are deploying, whatever, it takes forever. The problem is really that there is a spike in CPU usage.

The hot fix can be downloaded here:

http://support.microsoft.com/kb/943165

FIX: A CPU usage spike occurs on all the BizTalk hosts after you install security bulletin MS07-040 on a server that is running Microsoft BizTalk Server 2006 or Microsoft BizTalk Server 2006 R2

Don’t ever set port authentication if you are doing AS2, for your love that is all holy.

Event Type:    Error
Event Source:    BizTalk Server 2006
Event Category:    BizTalk Server 2006
Event ID:    5719
Date:        6/13/2009
Time:        12:54:19 AM
User:        N/A
Computer:   
Description:
There was a failure executing the receive pipeline: "Microsoft.BizTalk.EdiInt.DefaultPipelines.AS2EdiReceive, Microsoft.BizTalk.Edi.EdiIntPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "Pipeline " Receive Port: "AS2Receive" URI: "/BTSHTTPReceive.dll" Reason: The party corresponding to the inbound message cannot be identified.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

The fix is to set the receive port to No Authentication

You need to create a 32 bit host and its subsequent host instance, otherwise you will get the following error.

Event Type:    Error
Event Source:    BizTalk Server 2006
Event Category:    BizTalk Server 2006
Event ID:    5754
Date:        6/12/2009
Time:        7:37:50 PM
User:        N/A
Computer:    HAL64
Description:
A message sent to adapter "HTTP" on send port "SendPort4" with URI http://server/BTSHTTPReceive.dll is suspended.
Error details: There was a failure executing the send pipeline: "Microsoft.BizTalk.EdiInt.DefaultPipelines.AS2Send, Microsoft.BizTalk.Edi.EdiIntPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "Unknown " Send Port: "SendPort4" URI: http://server/BTSHTTPReceive.dll Reason: Retrieving the COM class factory for component with CLSID {254B4003-2AA7-4C82-BB2E-18BA7F22DCD2} failed due to the following error: 80040154. 
MessageId:  {9B3DB552-386E-4C46-859F-732AC39E1281}
InstanceID: {24442524-9A98-4778-A29A-93FA21AFC92B}

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I am sure y’all know (yes, I can say that, I am in Texas) that on the send side, there is a new advanced property. I discovered the following new line the administration console:

After looking into it further, there is a new feature in the advanced options called Schedule

I am not sure however, if there is a a way in which you can use this setting dynamically though.

For you BizTalkers (or wannabe BizTalkers), I will be doing a walk thru of Biztalk 2009, the bells, the whistles, and more importantly, the questions.

If you are interested, you can read about it here:

http://www.socalcodecamp.com/session.aspx?sid=a5fa2ebf-d56d-4461-a87f-3faae359f718

As one of the banners state: “Come Get Your Nerd On”

I am going to be speaking at the Desert Code Camp this weekend. If you are in the Surface Valley of the Sun this weekend, come see me and what BizTalk can do!

You can sign up for the EDI Batching tutorial here.

I found this method, I’ve used it a few times, so I’m going to put it here for my own use.

/// <summary>
/// Reads data from a stream until the end is reached. The
/// data is returned as a byte array. An IOException is
/// thrown if any of the underlying IO calls fail.
/// </summary>
/// <param name="stream">The stream to read data from</param>
/// <param name="initialLength">The initial buffer length</param>
public static byte[] ReadFully (Stream stream, int initialLength)
{
    // If we've been passed an unhelpful initial length, just
    // use 32K.
    if (initialLength < 1)
    {
        initialLength = 32768;
    }
    
    byte[] buffer = new byte[initialLength];
    int read=0;
    
    int chunk;
    while ( (chunk = stream.Read(buffer, read, buffer.Length-read)) > 0)
    {
        read += chunk;
        
        // If we've reached the end of our buffer, check to see if there's
        // any more information
        if (read == buffer.Length)
        {
            int nextByte = stream.ReadByte();
            
            // End of stream? If so, we're done
            if (nextByte==-1)
            {
                return buffer;
            }
            
            // Nope. Resize the buffer, put in the byte we've just
            // read, and continue
            byte[] newBuffer = new byte[buffer.Length*2];
            Array.Copy(buffer, newBuffer, buffer.Length);
            newBuffer[read]=(byte)nextByte;
            buffer = newBuffer;
            read++;
        }
    }
    // Buffer is now too big. Shrink it.
    byte[] ret = new byte[read];
    Array.Copy(buffer, ret, read);
    return ret;
}

This is actually a step by step tutorial on how to enable batching for outbound EDI transactions, where the input file needs to be split up from one single message but creating multiple transactions.

In my case I am going to take an invoice process. I am picking up a file from an FTP site with multiple rows, each row represents an invoice. Once all of the transactions are created, I need to release the batch.

The first think I need to do is create an orchestration that promotes the following context properties:

EDI.ToBeBatched
EDI.DestinationPartyId
EDI.EncodingType

Because I am going to be looping through a single message, I am unable to promote values in a loop, so I need to escape the loop to promote the values.

Create an orchestration that will be called from within the loop to send it to the Microsoft’s batching orchestration. In this case I am going to create a new project called InvoiceBatcher, and the in the project create an orchestration. Add the following references:

C:\Program Files\Microsoft BizTalk Server 2006\Microsoft.BizTalk.Edi.BaseArtifacts.dll
C:\Program Files\Microsoft BizTalk Server 2006\Microsoft.BizTalk.Edi.BatchingOrchestration.dll

Reference the EDI 810 project.

Setup objects and configuration for the orchestration

1. CorrelationType which contains the context properties

EDI.ToBeBatched
EDI.DestinationPartyId
EDI.EncodingType

2. Multi-part Message Type called EDIType representing the 810, also change the Type Modifier to Public (because we are going to be sending data to this orchestration outside of this project

3. EDIPortType which is a one way port type that points to the multi-part message type created in step 2.

4. Create a Correlation Set that is based on the CorrelationType created in step 1.

5. Create a message called OutEDIMsg that is based on the EDIType created in step 2.

6. Create a Port called Port and referencing the EDIPortType that was created in Step 3 and set it to Direct and direction set to Send.

7. Create an Int32 Orchestration Parameter called PartyNo with a direction of In

8. Create a Message Parameter called EDIMsg based on the EDIType created in step 2 with a direction of in.

9. Click on the Orchestration surface and change the Type Modifier to Public so it can be accessed by other orchestrations that will be referencing this assembly

Your orchestration view should look like this:

The orchestration surface should look like this:

The code in the Promote Context is this:

OutEDIMsg=EDIMsg;
OutEDIMsg(EDI.ToBeBatched)=true;
OutEDIMsg(EDI.DestinationPartyId)=PartyNo;
OutEDIMsg(EDI.EncodingType)=0;

On the Send EDI Message, send the OutEDIMsg and make sure that the Initializing Correlation Set is ‘CorrelationSet’

Deploy it to the BizTalk EDI Application

Next is to create the batch release process. The first thing we need to do is create a schema that represents the table in the management database.

Create a new project called PAM and create a schema called PAMRequest.xsd. Open the newly created schema in the XML editor, not the BizTalk Schema editor and paste the following code:

<?xml version="1.0"?>
<xs:schema xmlns:tns="http://PAM_Trigger" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://PAM_Trigger" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Request">
    <xs:complexType>
      <xs:sequence>
        <xs:element xmlns:updategram="urn:schemas-microsoft-com:xml-updategram" updategram:Prefix="updg" minOccurs="1" maxOccurs="unbounded" name="sync">
          <xs:complexType>
            <xs:sequence>
              <xs:element updategram:Prefix="updg" minOccurs="0" maxOccurs="unbounded" name="after">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" maxOccurs="unbounded" name="PAM_Control">
                      <xs:complexType>
                        <xs:attribute name="DestinationParty" type="xs:string" />
                        <xs:attribute name="EdiMessageType" type="xs:short" />
                        <xs:attribute name="ActionType" type="xs:string" />
                        <xs:attribute name="ActionDateTime" type="xs:string" />
                        <xs:attribute name="UsedOnce" type="xs:boolean" />
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="Response">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Success" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Deploy the project to the BizTalk EDI Application

Now to create an orchestration that will actually release the batch of transactions.

Create a new project called BatchRelease

Add a reference to the PAM assembly.

Create a new orchestration called ReleaseEDIBatch

Set the Type Modifier to Public so any other orchestration can reference it.

Create the following orchestration objects (from bottom to top)

PamResponseType (Multi-part Message Type)

PamRequestType (Multi-part Message Type)

PAMPortType (Request/Response Port Type)

TempXML (System.XML.XMLDocument)

PAMResponseMsg (PAMResponseType)

PAMRequestMsg (PAMRequestType)

PAMPort (Specify Later, Send-Recieve direction)

PartnerNo (Direction:In, Int32)

The Orchestration View should look like this:

The orchestration surface looks like this:

The logic to create the PAM message in the message assignment shape is:

TempXML=new System.Xml.XmlDocument();
TempXML.LoadXml("<ns0:Request xmlns:ns0=\"http://PAM_Trigger\"><ns0:sync><ns0:after><ns0:PAM_Control DestinationParty=\""+System.Convert.ToString(PartnerNo)+"\" EdiMessageType=\"0\" ActionType=\"EdiBatchOverride\" ActionDateTime=\""+System.Convert.ToString(System.DateTime.Now)+"\" UsedOnce=\"0\" /></ns0:after></ns0:sync></ns0:Request>");
PAMRequestMsg.MessagePart=TempXML;

Deploy it to the BizTalk EDI Application

Now to create the actual Invoice orchestration, first reference the InvoiceBatcher and the BatchRelease assemblies. Create the multi-part message that represents the input message, and the port type to bring in the file from the ftp server. I created the following variables all as Int32:

ThisInvoiceNo (default value set at 0)

PartyNumber

InvoiceCount

OriginalInformixMsg (Informix810Type)

NewInformixMsg (Inforix810Type)

EDIMsg (InvoiceBatcher.Multi-part Message Types.EDIType)

InformixPort

The following Orchestration View represents the setup.

The steps for this orchestration is to receive the file, count the number of invoices, set the party number, start looping while the current invoice number is less than the invoice count. In the loop, it increases the current invoice number, injects that number into the message. The map takes that newly created message and extracts the data from the input message and creates the 810 transaction. It then sends the transaction to be batched and continues creating new transactions. Once the loop completes, it releases the batch.

The orchestration looks like this:

The Set Party Number expression shape has the following code:

InvoiceCount=(System.Int32)xpath(OriginalInfomixMsg.MessagePart,"count(//Invoice)");
PartyNumber=1;

The Inject Number message assignment has the following code:

ThisInvoiceNo=ThisInvoiceNo+1;
NewInformixMsg.MessagePart=OriginalInfomixMsg.MessagePart;
xpath(NewInformixMsg.MessagePart,"/File/Value/text()")=System.Convert.ToString(ThisInvoiceNo);

The map looks like this:

The arguments to the Send EDI start orchestration shape (InvoiceBatcher.BatchPromote) are the following:

The arguments to the Release Batch start orchestration shape (BatchRelease.ReleaseEDIBatch) are the following:

The last thing of mention is to set up the send port for the ReleaseEDIBatch orchestration to bind to.

In the BizTalk EDI Application, create a request response send port set it to SQL and point to the BizTalk Management database. Sent the send and receive pipelines to DefaultXML

Set the following connection information as follows:

Bind to it and you are ready to go!

I remember searching for this T-SQL statement a few months ago, so time to put it on the blog.

--GET FIRST DAY OF LAST MONTH 
SELECT DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1,GETDATE())), '01/01/2000') as [Date of First Day of Last Month] 
--GET LAST DAY OF LAST MONTH 
SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,'01/01/2000',GETDATE()),'01/01/2000')) as [Date of Last Day of Last Month]

Since it is using OLE, you can connect to whatever data source you want.

Below is a brief list of sources to connect to:

You can access a lot more sources. Here is a list of sources you can check against:

Connection Strings

Another thing is that if you wanted to put a where clause in your statement to limit the data that is being returned, the forth argument in the Database Lookup functiod is the place to put it. BEWARE, you need to make sure that the column that you are matching with the first argument is correct.

Here is the functiod description for the Database Lookup functiod:

Use the Database Lookup functoid to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset. This functoid requires the following four input parameters in this order: a lookup value, a database connection string, a table name, and a column name for the lookup value.

1. Lookup Value
2. Database Connection String
3. Table Name
4. Column Name

I did not want to create a view, as I don’t have permissions to add objects to that database, but I could not simply get the value from one table, I needed to join two tables together.

The answer was SIMPLE!

Underneath the covers, the statement that is being called is:

using (OleDbCommand command = new OleDbCommand("SELECT * FROM " + table + " WHERE " + column + "= ?", helper.Connection))
{
...
}

WAIT A MINUTE! I am not limited to only a table name in the table variable of the OleDbCommand method, I can put an entire join statement in argument 3 of the Database Lookup functiod.

Here is what I had for the arguments:

1. Source Data
2. Connection Information (in this case I just pointed to a udl file): ‘File Name=D:\Eric\Warehouse\Schemas\connection.udl’
3. Table name, which has the join: ‘EdiPartnerContacts INNER JOIN bts_party ON EdiPartnerContacts.PartyId = bts_party.nID’
4. Column that has the input value: ‘nvcName’

So in essence I had created the following query using the DBLookup functiod

SELECT     * 
FROM         EdiPartnerContacts INNER JOIN 
                      bts_party ON EdiPartnerContacts.PartyId = bts_party.nID

WHERE       nvcName=?

I then pulled the Email column from the data in a Value Extractor that was connected to the Database Lookup functiod

There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Receive Port: "EDI837P_ReceivePort1" URI: "\\SERVER\folder\*.xml" Reason: Finding the document specification by message type "EDIMsg" failed. Verify the schema deployed properly.

Make sure that you don’t have the same schema deployed twice!

Recently I got the following error while trying to write out a flat file.  The way I resolved it was to remove the .dll from the GAC and resinstall the .msi.

Event Type:    Error
Event Source:    XLANG/s
Event Category:    None
Event ID:    10025
Date:        4/28/2009
Time:        12:01:17 PM
User:        N/A
Computer:    YOURCOMPUTER

Description:
Uncaught exception (see the 'inner exception' below) has suspended an instance of service YOURPROCESSNAME'.
The service instance will remain suspended until administratively resumed or terminated.
If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.
InstanceId: 3f85458e-35c0-4f56-bce9-c4dc7a054f5f
Shape name: Send msg_FlatFile
ShapeId: 65a1739e-9b12-41c2-abb1-1aa2776d2657
Exception thrown from: segment 1, progress 22
Inner exception: Failed while attempting to send message 'msg_FlatFile'.
Exception type: SendFailedException
Source: Microsoft.XLANGs.BizTalk.Engine
Target Site: Void WriteMessageState(Microsoft.BizTalk.Interop.IBTPEPInfoLookup, System.Guid, Microsoft.XLANGs.BaseTypes.XLANGMessage, Microsoft.XLANGs.Core.Segment, System.String, System.String, System.Collections.IList, Boolean, System.Collections.IList)
The following is a stack trace that identifies the location where the exception occured

   at Microsoft.BizTalk.XLANGs.BTXEngine.BTXXlangStore.WriteMessageState(IBTPEPInfoLookup pepLookup, Guid portId, XLANGMessage msg, Segment seg, String opname, String url, IList promoteProps, Boolean track, IList toPromote)
   at Microsoft.BizTalk.XLANGs.BTXEngine.BTXLogicalPortBinding.SendMessage(XLANGMessage msg, XlangStore store, Segment seg, OperationInfo op, IList additionalProps, IList toPromote, Boolean ignoreRoutingFailure)
   at Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.SendMessage(Int32 iOperation, XLANGMessage msg, Correlation[] initCorrelations, Correlation[] followCorrelations, Context cxt, Segment seg, ActivityFlags flags)
   at YOURORCH.segment1(StopConditions stopOn)
   at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s, StopConditions stopCond, Exception& exp)
Additional error information:

        Exception from HRESULT: 0xC0C01672
Exception type: COMException
Source: Microsoft.XLANGs.BizTalk.Engine
Target Site: Void PostToSendPort(System.Guid ByRef, Microsoft.BizTalk.Agent.Interop.IBTMessageBatch, Microsoft.BizTalk.Agent.Interop.IBTMessage, System.String, System.String, Microsoft.BizTalk.Interop.IBTMMessageList ByRef)
The following is a stack trace that identifies the location where the exception occured

   at Microsoft.BizTalk.Interop.IBTPEPInfoLookup.PostToSendPort(Guid& sendPortID, IBTMessageBatch batch, IBTMessage msg, String operationName, String url, IBTMMessageList& dlMsgList)
   at Microsoft.BizTalk.XLANGs.BTXEngine.BTXXlangStore.WriteMessageState(IBTPEPInfoLookup pepLookup, Guid portId, XLANGMessage msg, Segment seg, String opname, String url, IList promoteProps, Boolean track, IList toPromote)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I needed to do a replace using System.RegularExpressions.Regex.Replace() but was finding that if I passed the replacement string from a form, it would not correctly account for the Hexadecimal values I specified correctly.  I noticed Expresso was having this same issue shown below:

Instead of replacing \r\n (Carriage Return Line Feed) with a “,” (Hex 2C), it was replacing it with the literal.  I noticed this only happened when you passed the replace string in through a form; it would not happen if you hard coded Regex.Replace(“\r\n”,”\x2C”) into your .NET code.

I had to create a method that cleaned up all HEX replace strings from a form.  If you have a better way of doing this, please comment.

public string CleanUpRegexReplaceStringFromForm(string str_DataFromForm)
{
    //     \n   The newline character. (ASCII 10) 
    str_DataFromForm = str_DataFromForm.Replace("\\n", "\n");
    //     \r   The carriage return character. (ASCII 13) 
    str_DataFromForm = str_DataFromForm.Replace("\\r", "\r");
    //     \t   The tab character. (ASCII 9)
    str_DataFromForm = str_DataFromForm.Replace("\\t", "\t");
    //     \x2C The , character.
    str_DataFromForm = str_DataFromForm.Replace("\\t", "\t").Replace("\\x00","\x00");
    //     \x   All other ASCII Characters
    str_DataFromForm = str_DataFromForm.Replace("\\x00","\x00");
    str_DataFromForm = str_DataFromForm.Replace("\\x01","\x01");
    str_DataFromForm = str_DataFromForm.Replace("\\x02","\x02");
    str_DataFromForm = str_DataFromForm.Replace("\\x03","\x03");
    str_DataFromForm = str_DataFromForm.Replace("\\x04","\x04");
    str_DataFromForm = str_DataFromForm.Replace("\\x05","\x05");
    str_DataFromForm = str_DataFromForm.Replace("\\x06","\x06");
    str_DataFromForm = str_DataFromForm.Replace("\\x07","\x07");
    str_DataFromForm = str_DataFromForm.Replace("\\x08","\x08");
    str_DataFromForm = str_DataFromForm.Replace("\\x09","\x09");
    str_DataFromForm = str_DataFromForm.Replace("\\x0A","\x0A");
    str_DataFromForm = str_DataFromForm.Replace("\\x0B","\x0B");
    str_DataFromForm = str_DataFromForm.Replace("\\x0C","\x0C");
    str_DataFromForm = str_DataFromForm.Replace("\\x0D","\x0D");
    str_DataFromForm = str_DataFromForm.Replace("\\x0E","\x0E");
    str_DataFromForm = str_DataFromForm.Replace("\\x0F","\x0F");
    str_DataFromForm = str_DataFromForm.Replace("\\x10","\x10");
    str_DataFromForm = str_DataFromForm.Replace("\\x11","\x11");
    str_DataFromForm = str_DataFromForm.Replace("\\x12","\x12");
    str_DataFromForm = str_DataFromForm.Replace("\\x13","\x13");
    str_DataFromForm = str_DataFromForm.Replace("\\x14","\x14");
    str_DataFromForm = str_DataFromForm.Replace("\\x15","\x15");
    str_DataFromForm = str_DataFromForm.Replace("\\x16","\x16");
    str_DataFromForm = str_DataFromForm.Replace("\\x17","\x17");
    str_DataFromForm = str_DataFromForm.Replace("\\x18","\x18");
    str_DataFromForm = str_DataFromForm.Replace("\\x19","\x19");
    str_DataFromForm = str_DataFromForm.Replace("\\x1A","\x1A");
    str_DataFromForm = str_DataFromForm.Replace("\\x1B","\x1B");
    str_DataFromForm = str_DataFromForm.Replace("\\x1C","\x1C");
    str_DataFromForm = str_DataFromForm.Replace("\\x1D","\x1D");
    str_DataFromForm = str_DataFromForm.Replace("\\x1E","\x1E");
    str_DataFromForm = str_DataFromForm.Replace("\\x1F","\x1F");
    str_DataFromForm = str_DataFromForm.Replace("\\x20","\x20");
    str_DataFromForm = str_DataFromForm.Replace("\\x21","\x21");
    str_DataFromForm = str_DataFromForm.Replace("\\x22","\x22");
    str_DataFromForm = str_DataFromForm.Replace("\\x23","\x23");
    str_DataFromForm = str_DataFromForm.Replace("\\x24","\x24");
    str_DataFromForm = str_DataFromForm.Replace("\\x25","\x25");
    str_DataFromForm = str_DataFromForm.Replace("\\x26","\x26");
    str_DataFromForm = str_DataFromForm.Replace("\\x27","\x27");
    str_DataFromForm = str_DataFromForm.Replace("\\x28","\x28");
    str_DataFromForm = str_DataFromForm.Replace("\\x29","\x29");
    str_DataFromForm = str_DataFromForm.Replace("\\x2A","\x2A");
    str_DataFromForm = str_DataFromForm.Replace("\\x2B","\x2B");
    str_DataFromForm = str_DataFromForm.Replace("\\x2C","\x2C");
    str_DataFromForm = str_DataFromForm.Replace("\\x2D","\x2D");
    str_DataFromForm = str_DataFromForm.Replace("\\x2E","\x2E");
    str_DataFromForm = str_DataFromForm.Replace("\\x2F","\x2F");
    str_DataFromForm = str_DataFromForm.Replace("\\x30","\x30");
    str_DataFromForm = str_DataFromForm.Replace("\\x31","\x31");
    str_DataFromForm = str_DataFromForm.Replace("\\x32","\x32");
    str_DataFromForm = str_DataFromForm.Replace("\\x33","\x33");
    str_DataFromForm = str_DataFromForm.Replace("\\x34","\x34");
    str_DataFromForm = str_DataFromForm.Replace("\\x35","\x35");
    str_DataFromForm = str_DataFromForm.Replace("\\x36","\x36");
    str_DataFromForm = str_DataFromForm.Replace("\\x37","\x37");
    str_DataFromForm = str_DataFromForm.Replace("\\x38","\x38");
    str_DataFromForm = str_DataFromForm.Replace("\\x39","\x39");
    str_DataFromForm = str_DataFromForm.Replace("\\x3A","\x3A");
    str_DataFromForm = str_DataFromForm.Replace("\\x3B","\x3B");
    str_DataFromForm = str_DataFromForm.Replace("\\x3C","\x3C");
    str_DataFromForm = str_DataFromForm.Replace("\\x3D","\x3D");
    str_DataFromForm = str_DataFromForm.Replace("\\x3E","\x3E");
    str_DataFromForm = str_DataFromForm.Replace("\\x3F","\x3F");
    str_DataFromForm = str_DataFromForm.Replace("\\x40","\x40");
    str_DataFromForm = str_DataFromForm.Replace("\\x41","\x41");
    str_DataFromForm = str_DataFromForm.Replace("\\x42","\x42");
    str_DataFromForm = str_DataFromForm.Replace("\\x43","\x43");
    str_DataFromForm = str_DataFromForm.Replace("\\x44","\x44");
    str_DataFromForm = str_DataFromForm.Replace("\\x45","\x45");
    str_DataFromForm = str_DataFromForm.Replace("\\x46","\x46");
    str_DataFromForm = str_DataFromForm.Replace("\\x47","\x47");
    str_DataFromForm = str_DataFromForm.Replace("\\x48","\x48");
    str_DataFromForm = str_DataFromForm.Replace("\\x49","\x49");
    str_DataFromForm = str_DataFromForm.Replace("\\x4A","\x4A");
    str_DataFromForm = str_DataFromForm.Replace("\\x4B","\x4B");
    str_DataFromForm = str_DataFromForm.Replace("\\x4C","\x4C");
    str_DataFromForm = str_DataFromForm.Replace("\\x4D","\x4D");
    str_DataFromForm = str_DataFromForm.Replace("\\x4E","\x4E");
    str_DataFromForm = str_DataFromForm.Replace("\\x4F","\x4F");
    str_DataFromForm = str_DataFromForm.Replace("\\x50","\x50");
    str_DataFromForm = str_DataFromForm.Replace("\\x51","\x51");
    str_DataFromForm = str_DataFromForm.Replace("\\x52","\x52");
    str_DataFromForm = str_DataFromForm.Replace("\\x53","\x53");
    str_DataFromForm = str_DataFromForm.Replace("\\x54","\x54");
    str_DataFromForm = str_DataFromForm.Replace("\\x55","\x55");
    str_DataFromForm = str_DataFromForm.Replace("\\x56","\x56");
    str_DataFromForm = str_DataFromForm.Replace("\\x57","\x57");
    str_DataFromForm = str_DataFromForm.Replace("\\x58","\x58");
    str_DataFromForm = str_DataFromForm.Replace("\\x59","\x59");
    str_DataFromForm = str_DataFromForm.Replace("\\x5A","\x5A");
    str_DataFromForm = str_DataFromForm.Replace("\\x5B","\x5B");
    str_DataFromForm = str_DataFromForm.Replace("\\x5C","\x5C");
    str_DataFromForm = str_DataFromForm.Replace("\\x5D","\x5D");
    str_DataFromForm = str_DataFromForm.Replace("\\x5E","\x5E");
    str_DataFromForm = str_DataFromForm.Replace("\\x5F","\x5F");
    str_DataFromForm = str_DataFromForm.Replace("\\x60","\x60");
    str_DataFromForm = str_DataFromForm.Replace("\\x61","\x61");
    str_DataFromForm = str_DataFromForm.Replace("\\x62","\x62");
    str_DataFromForm = str_DataFromForm.Replace("\\x63","\x63");
    str_DataFromForm = str_DataFromForm.Replace("\\x64","\x64");
    str_DataFromForm = str_DataFromForm.Replace("\\x65","\x65");
    str_DataFromForm = str_DataFromForm.Replace("\\x66","\x66");
    str_DataFromForm = str_DataFromForm.Replace("\\x67","\x67");
    str_DataFromForm = str_DataFromForm.Replace("\\x68","\x68");
    str_DataFromForm = str_DataFromForm.Replace("\\x69","\x69");
    str_DataFromForm = str_DataFromForm.Replace("\\x6A","\x6A");
    str_DataFromForm = str_DataFromForm.Replace("\\x6B","\x6B");
    str_DataFromForm = str_DataFromForm.Replace("\\x6C","\x6C");
    str_DataFromForm = str_DataFromForm.Replace("\\x6D","\x6D");
    str_DataFromForm = str_DataFromForm.Replace("\\x6E","\x6E");
    str_DataFromForm = str_DataFromForm.Replace("\\x6F","\x6F");
    str_DataFromForm = str_DataFromForm.Replace("\\x70","\x70");
    str_DataFromForm = str_DataFromForm.Replace("\\x71","\x71");
    str_DataFromForm = str_DataFromForm.Replace("\\x72","\x72");
    str_DataFromForm = str_DataFromForm.Replace("\\x73","\x73");
    str_DataFromForm = str_DataFromForm.Replace("\\x74","\x74");
    str_DataFromForm = str_DataFromForm.Replace("\\x75","\x75");
    str_DataFromForm = str_DataFromForm.Replace("\\x76","\x76");
    str_DataFromForm = str_DataFromForm.Replace("\\x77","\x77");
    str_DataFromForm = str_DataFromForm.Replace("\\x78","\x78");
    str_DataFromForm = str_DataFromForm.Replace("\\x79","\x79");
    str_DataFromForm = str_DataFromForm.Replace("\\x7A","\x7A");
    str_DataFromForm = str_DataFromForm.Replace("\\x7B","\x7B");
    str_DataFromForm = str_DataFromForm.Replace("\\x7C","\x7C");
    str_DataFromForm = str_DataFromForm.Replace("\\x7D","\x7D");
    str_DataFromForm = str_DataFromForm.Replace("\\x7E","\x7E");
    str_DataFromForm = str_DataFromForm.Replace("\\x7F", "\x7F"); 
    return str_DataFromForm;
}

For those of you who are using the multiple schema to split your claims up into individual messages, you might have come across the performance degradation issue with having those messages come into the message box.

This functionality worked fine using the Covast HIPAA Accelerator, but seemed to be a huge issue when migrating to R2.

This is the cause that Microsoft states:

The splitting behavior results in XML files that include not only the sub-document that you want, but also empty XML markup for all sibling sub-documents. Although the sibling sub-documents do not contain data, the behavior results in bloated output XML files and other inefficiencies that make processing very large EDI batches impossible.

I think it is interesting that something is impossible – wow!

To make the impossible possible again, you need to install hot fix 967945, you can download it in the View and request hotfix downloads link.

I’ve added some functionality to the PGP Pipeline component to enable it to Sign and Encrypt files.

Properties Explained:

ASCIIArmorFlag – Writes out file in ASCII or Binary

Extension – Final File’s extension

Operation – Decrypt, Encrypt, and now Sign and Encrypt

Passphrase – Private Key’s password for decrypting and signing

PrivateKeyFile – Absolute path to private key file

PublicKeyFile – Absolute path to public key file.

TempDirectory – Temporary directory used for file processing.

Email me if you could use this.

I was having some problems transferring files from a very old SUN Unix server using the Microsoft provided FTP adapter.  I also needed the ability to transfer files to a FTP over SSL enabled server.  There seemed to be only a few supported FTP server types for the MS FTP adapter listed here:

• Microsoft Windows Server 2003
• Windows 2000 Server Service Pack 3 (SP3) and later versions
• Windows 2000 Advanced Server SP3 and later versions
• Sun Solaris 9.0
• HP-UX
• Redhat 7.x Linux
• IBM O/S 390 running MVS
• AS/400 OS/400 V5R1

I decided to create a new BizTalk FTP/FTPS adapter that would be robust and allow me to connect to both FTP and FTPS (FTP over SSL or FTP-SSL) servers. It also doesn’t have the limitation on server OSs that the MS FTP adapter has.

Here are the Receive Location Properties.

Explanation of properties:

CRLF Mode - The CRLF Mode  property applies when downloading files in ASCII mode. If CRLF Mode is set to No Alteration the transfer happens normally without alteration. A value of CRLF converts all line endings to CR+ LF. A value of LF Only converts all line endings to LF-only. A value of CR Only converts all line endings to CR-only.

FTP Trace Mode – Send a trace of the FTP session and any errors to either a File, designated by the FTP Trace path and FileName, Event Log, Both, or None.

Transfer Mode – Binary or ASCII

Use Passive Host Address - Some FTP servers need this option for passive data transfers. In passive mode, the data connection is initiated by the client sending a PASV command to the FTP server, and the FTP server responds with the IP address and port number where it is listening for the client's connection request. When the Use Passive Host Address property is set to Yes, the IP address in the PASV response is discarded and the IP address of the remote endpoint of the existing control connection is used instead.

Authentication Mode - By setting the Authentication Mode Property to AuthTls , a secure FTP connection can be established using either SSL 3.0 or TLS 1.0. The FTP_FTPS Adapter will automatically choose whichever is supported by the FTP server during the secure channel establishment. The FTP control port remains at the default (21). Upon connection, the channel is converted to a secure channel automatically. All control messages and data transfers are encrypted. By choosing Implicit SSL, the FTP_FTPS Adapter connects using SSL on port 990, which is the de-facto standard FTP SSL port.

Client Certificate – The FTP_FTPS Adapter provides the ability to use a client certificate with secure FTP (implicit or explicit SSL/TLS).

Clear Control Channel - Reverts the FTP control channel from SSL/TLS to an unencrypted channel. This may be required when using FTPS with AUTH TLS where the FTP client is behind a DSL or cable-modem router that performs NAT (network address translation). If the control channel is encrypted, the router is unable to translate the IP address sent in the PORT command for data transfers. By clearing the control channel, the data transfers will remain encrypted, but the FTP commands are passed unencrypted.

Private Key File – The FTP_FTPS Adapter provides the ability to use a client certificate with secure FTP (implicit or explicit SSL/TLS). You may load a certificate from separate .crt (or .cer) and .pvk files and use it as the client-side SSL cert. The .pvk contains the private key. The .crt/.cer file contains the PEM or DER encoded digital certificate. Note: Client-side certificates are only needed in situations where the server demands one.

Invoice VAN FTP/SSL – By choosing yes, the FTP_FTPS Adapter sets all the properties correctly to connect to an Inovis VAN FTP/SSL.

Tumbleweed Certificate Common Name– The FTP_FTPS Server  can connect, authenticate, transfer files to a Tumbleweed Secure Transport SSL FTP Server. Instead of providing a login name and password, you pass the string "site-auth" for the username, and an empty string for the password. You must also provide a client-side digital certificate -- as the certificate's credentials and validity are used to authenticate.

MODE Z – The FTP/FTPS Adapter automatically detects if the FTP server supports MODE Z.  It allows for files to be uploaded and downloaded using compressed streams.

SOCKS Version - Both SSL/TLS and non-secure FTP communications may use SOCKS4 and SOCKS5 proxies. Choose which version of SOCKS and provide the SOCKS Proxy Host Name, SOCKS Proxy Password, SOCKS Proxy Port, and SOCKS Proxy User Name.

Active Port End Range - When Active FTP Mode is used, the client-side is responsible for choosing a random port for each data connection. (Note: In the FTP protocol, each data transfer occurs on a separate TCP/IP connection. Commands are sent over the control channel (port 21 for non-SSL, port 990 for SSL).)

Active Port Start Range - This property, along with Active Port End Range, allows the client to specify a range of ports for data connections when in Active mode.

Proxy Mode - The proxy scheme used by your FTP proxy server. Valid values are 0 to 8. Supported proxy methods are as follows:

Note: The Proxy Host Name is the hostname of the firewall, if the proxy is a firewall. Also, the Proxy User Name and Proxy Password are the firewall username/password (if the proxy is a firewall).

ProxyMethod = 1 (SITE site)

USER ProxyUsername
PASS ProxyPassword
SITE Hostname
USER Username
PASS Password

ProxyMethod = 2 (USER user@site)

USER Username@Hostname:Port
PASS Password

ProxyMethod = 3 (USER with login)

USER ProxyUsername
PASS ProxyPassword
USER Username@Hostname:Port
PASS Password

ProxyMethod = 4 (USER/PASS/ACCT)

USER Username@Hostname:Port ProxyUsername
PASS Password
ACCT ProxyPassword

ProxyMethod = 5 (OPEN site)

USER ProxyUsername
PASS ProxyPassword
OPEN Hostname
USER Username
PASS Password

ProxyMethod = 6 (firewallId@site)

USER ProxyUsername@Hostname
USER Username
PASS Password

ProxyMethod = 7

USER ProxyUsername
USER ProxyPassword
SITE Hostname:Port USER Username
PASS Password

ProxyMethod = 8

USER Username@ProxyUsername@Hostname
PASS Password@ProxyPassword

Detect FTP Authentication - Determines what combinations of FTP/FTPS property settings result in successful data transfers. The FTP_FTPS Adapter tries 13 different combinations of these properties: Ssl, AuthTls, AuthSsl, Port, Passive, and Use Passive Host Address. Within the FTP protocol, the process of fetching a directory listing is also considered a "data transfer". The FTP_FTPS Adapter method works by checking to see which combinations result in a successful directory listing download. The FTP_FTPS Adapter requires the Host Name, Username, Password, and Port and returns a string containing an XML report of the results. It is a blocking call that may take approximately a minute to run.  It is executed via the following screen that opens when the ellipses is pressed.

Note:  When uploading files, the FTP/FTPS adapter uses a .TMP file for any file over 4k.  If you have a process that pulls *.* from the folder you are sending to, it might cause unintended consequences if the .TMP is removed before the FTP/FTPS adapter can rename it.

The FTP/FTPS Adapter supports dynamic sends.

Please email me if you need another setting for your particular FTP(S) server.

You can buy the FTP/FTPS adapter here.  It is pretty reasonably priced given the many hours I spent developing and testing it.

If you have issues with the ftp adapter, below listed are a few hot fixes from Microsoft:

The FTP adapter stops processing the file transfer in a Microsoft BizTalk Server 2006 environment after a network connection recovers

The BizTalk FTP Adapter reports that no files are available even though the files exist when you try to connect to a GXS server in BizTalk Server 2006 R2

The BizTalk 2006 FTP adapter does not retrieve a file from an FTP server when you issue the RETR command

If there are any more out there, let me know…

By default tracking in BizTalk is enabled for a couple of components that are useful during development and testing, but mostly overkill for a process that is solid, and has been running for a while.

The first thing is to create a separate host that is dedicated solely for tracking:

The other things to increase performance is to disable tracking in orchestrations:

The third thing is to disable the pipeline tracking:

I must have changed my password to my email server a LONG time ago, because it has been ages since I have been getting emails from my blog.

I finally took time to look into this and have reconfigured it to send me emails again.

If there are any unanswered emails out there, please let me know and I will answer them.

Sorry, as I do really want to hear from you!

I ran a file that had a null character in it, and this is the error that I received:

<?xml version="1.0"?>
<Result State="FAILED">
    <Error>
        <HResult>0xC00CEE2B</HResult>
        <Description>
            <![CDATA[Illegal xml character.
            ]]>
        </Description>
        <Source>XML SAX Parser</Source>
        <Type>FATAL</Type>
    </Error>
</Result>

I’ve many times needed to find a value within an entire database.

Here’s a stored procedure that accomplishes that.

CREATE PROCEDURE [dbo].[FindMatchingValueInAnyTable]
(
    @Value VARCHAR(64)
)
AS
BEGIN
    DECLARE @sql VARCHAR(MAX)
    DECLARE @TableName VARCHAR(64)
    DECLARE @ColumnName VARCHAR(64)
    CREATE TABLE #Results
    (
        TableName VARCHAR(64)
    ,    ColumnName VARCHAR(64)
    )
    DECLARE TABLES CURSOR
    FOR
    SELECT
        o.name
    ,    c.name
    FROM syscolumns c
    INNER JOIN sysobjects o
    ON c.id = o.id
    WHERE o.type = 'U'
    AND c.xtype IN (167, 175, 231, 239)
    ORDER BY
        o.name
    ,    c.name
    OPEN TABLES
    FETCH NEXT FROM TABLES
    INTO @TableName
    ,    @ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @TableName + '] '
        SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @ColumnName + '])) LIKE ''%' + @value + '%'') '
        SET @sql = @sql + 'INSERT INTO #Results ( TableName, ColumnName ) VALUES (''' + @TableName + ''', '''
        SET @sql = @sql + @ColumnName + ''')'
        EXEC(@sql)
    FETCH NEXT FROM TABLES
    INTO @TableName
    ,    @ColumnName
    END
    CLOSE TABLES
    DEALLOCATE TABLES
    SELECT *
    FROM #Results
    DROP TABLE #Results
END

I was attempting to debug a stored procedure that calls CLR functions (however this story can be told for any CLR object) and the debugger would simply hang.

There are two steps to get around this issue:

First is to disable and re-enable CLR debugging. To do this, in Visual Studio right click the SQL Server and uncheck the Allow SQL/CLR Debugging line.

Then re enable it, where you will see the following dialog where you should press Yes.

The second step is to disable affinity for multiple cpu machines.

In SQL Management Studio, right click the server in the tree, and go to properties. There click on the Processors, Uncheck the Automatically set processor affinity mask for all processors:

This worked for me. I am wondering if there is a hotfix for this?