Eric's BizTalk 2004/2006 blog

No results when searching for A word or phrase in the file

Eric Stott — Wed, 02 Jul 2008 06:50:12 GMT

Have you ever used the search functionality in Windows Explorer, only to be disappointed as it returns no results. I've seen this a million times, and is one of the first settings I change when bring up a server.

Here's the fix:

set the FilterFilesWithUnknownExtensions DWORD value to 1 in the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ContentIndex

BizTalk 2004 SP2 Upgrade

Eric Stott — Tue, 17 Jun 2008 03:47:30 GMT

I installed an SP2 upgrade over the weekend on BizTalk 2004. Here were the take aways.

1. Always have the BizTalk user account as God mode for the SQL server that hosts the BizTalk Databases.

2. Shrink the MsgBox and Tracking Databases before installing the SP2 upgrade. (http://msdn2.microsoft.com/en-us/library/bb203857.aspx for the MsgBoxDb and KB894253 for the Tracking DB)

3. If the database file sizes don't decrease use the following SQL methods:

dbcc shrinkdatabase(<DatabaseName>)
dbcc shrinkfile (<DatabaseName>, <size in MB>)

Replace any Character or String BizTalk Pipeline Component

Eric Stott — Tue, 03 Jun 2008 16:53:29 GMT

I've created a decode BizTalk Pipeline Component that can replace any character or any string in an inbound file.

Here are some uses for it:

Replace a Trading Partner ID before BizTalk receives the file in the Message Box.

Replace HEX Characters in a flat file.

Insert carriage returns in an EDI file as the Segment Terminator.

In Pipeline Properties choose the characters you’d like to replace in the form of <character to replace>,<replacement character>. You can replace as many character pairs as you’d like.

In this example I'm replacing » with N and a with w.

Here's a sample input file:

Here's the output:

Email me if you would like a copy.

Convert existing 2004 or 2006 HIPAA, BaseEDI, or Covast maps to BizTalk Server 2006 R2

Eric Stott — Mon, 02 Jun 2008 19:05:39 GMT

I am on a project that requires that I take all EDI maps that were created using BizTalk preR2 and convert them to the new schemas.

I looked here to be disappointed to find that everything except maps were listed.

I started re mapping (dragging lines again) and it took me an entire day to finish a map. (Yes my employer paid me 8 hours to do something I leaned in kindergarten). I have 30 more maps to go!

Instead, I created an application that will convert them automatically:

Here is what I started off with:

I replaced the schema and was left with this:

This is where Kindergarten came in handy (Thanks Mrs. Carlson!), I bagged it and created this program that automated the process.

Here is the result of the conversion:

Contact me if you would like to save yourself 5 to 8 hours per map (pricing is documented here)!

UltraEdit replace functionality

Eric Stott — Fri, 30 May 2008 16:49:21 GMT

In dealing with some EDI data, I came across some data that was invalid

In a file we had a large number of COB segments that looked like this:

COB*U*1234567*01*1

There are supposed to be 3 elements in the COB, so I hacked away at it for a while and came up with this command

Find COB^*U^**^*01^*1

With COB*U*00000000000*1

(the ^* means don’t use the expression * to mean all characters, actually look for * in the data, except where it says ^**^* where there is a *, but consume all data until you find the next *)

But I was not satisfied; I wanted the value from COB02 in the result.

To consume data into a ‘variable’ you use the following syntax

Find COB^*U^*^(*^)^*01^*1

With COB*U*^1*1

the ^(*^) loads whatever is between ^(^) (in this case *) into a variable, and because it is the first one, the output you specify is ^1

So in theory I could have this syntax

Find COB^*U^*^(*^)^*01^*^(*^)

With COB*U*^1*^2

The resulting output would be

COB*U*1234567*1

Unable to read the stream produced by the pipeline. There was no xml start tag open

Eric Stott — Thu, 22 May 2008 14:22:49 GMT

Here is the scenario to create this error:

You are batching outbound EDI documents, you have enabled the extended validation.

Resolution or Workaround:

=====================

The workaround is to disable extended validation of the batched messages. Product team will fix this in the next version of the product.

Inbound HIPAA filename capture

Eric Stott — Wed, 07 May 2008 05:40:57 GMT

I received an email today about this entry:

I have written a biztalk application ( biztalk 2004) which uses hipaa adapter 3.0 to accept a 835 claim payment schema.The application works fine when edi file supports single transaction set(ST/SE). When i try to process file with multiple ST/SE it reads the first set but ignores the rest.

I also use the logic you've provided in your blog to capture file name of the document using correlation set.

The error logged in the event log is as follows

Error encountered: ERROR (121), interchangenr 10744 :

An error occurred in the BizTalk Server 2004 connector. Check the details.

Document was rejected by BizTalk

What you have to do is set up a correlation set and capture multiple transactions, have a loop shape and receive multiple transactions (each having the same control number (ISA13), and have a timeout value and once that timeout occurs end the orchestration.

MSDN BizTalk Blog

Eric Stott — Mon, 05 May 2008 10:27:56 GMT

It looks like the BizTalk blog at msdn is new. Take a look.

Business Rules Engine - I WANT TO BELIEVE

Eric Stott — Mon, 28 Apr 2008 22:51:50 GMT

This is a call to anyone who happens upon my blog, thru my ~~contact page~~ the feedback section, let me know why I would want to use the Business Rule Engine.

As I see it, it seems like it is a lot of work to do something that can be done using SQL, C#, etc. In many cases, you have to know SQL, C#, etc to implement the BRE anyway.

Why would I add a layer of logic on top of existing code to get the same result?

Thanks for the feedback!

Upgrade Windows SDK link

Eric Stott — Thu, 24 Apr 2008 16:56:47 GMT

If you are reading the upgrade documentation

http://www.microsoft.com/downloads/details.aspx?FamilyID=df2e8a88-fb23-49a4-9ac7-d17f72517d12&displaylang=en

(Upgrading to BizTalk Server 2006 R2 from BizTalk Server 2006.doc)

"To apply the HIPAA33ForR2.mst transform"

You can download the Windows SDK from http://go.microsoft.com/fwlink/?linkid=75636

That link doesn't work, you need to go here and choose the right environment:

http://msdn2.microsoft.com/en-us/windowsserver/bb980924.aspx

The MSDN Development Center for Windows SDK is new, so browse around.

Thanks windows SDK blog for the quick response!

BizTalk Server 2006 R3

Eric Stott — Thu, 24 Apr 2008 00:11:58 GMT

Microsoft has made it official, R3 is coming:

Over the past few months we’ve seen an incredible wave of new platform products and technologies including Windows Server 2008, .NET Framework 3.5, Visual Studio 2008 and SQL Server 2008. You may remember that in the past BizTalk Server has updated features to support the most recent platform releases. With BizTalk Server 2006 once the RTM versions of Visual Studio 2005 and SQL Server 2005 were released, we did final testing and sent a new version of BizTalk Server out the door.

Based on feedback from customers and partners, we are planning to do the same thing this time around to follow closely on the heels of this latest platform wave. Once SQL Server 2008 (the final of the three to be released) becomes generally available, we will follow with an updated version of BizTalk Server designed for the latest versions of Windows Server, the .NET Framework, Visual Studio, and SQL Server. This will mean that BizTalk Server customers will continue to take full advantage of the platform’s latest improvements including: scalability for mission-critical workloads, improved support for next-generation web and service oriented applications, improved virtualization support and better business insight through Office.

We are also taking the opportunity as part of this BizTalk Server release to deliver additional customer-requested capabilities for our core SOA infrastructure. We will give more updates on specific features in the coming months, but at a high level we are planning some new investments in the release that includes:

New web service registry capabilities with support for UDDI (Universal Description Discovery and Integration) version 3.0
Enhanced service enablement of applications (through new and enhanced adapters for LOB applications, databases, and legacy/host systems)
Enhanced service enablement of “edge” devices through BizTalk RFID Mobile
Enhanced interoperability and connectivity support for B2B protocols (like SWIFT, EDI, etc)
SOA patterns and best practices guidance to assist our customer’s implementations

To ensure existing BTS applications continue to run without changes, we are taking an incremental approach and limiting the changes to the items listed above. Because this release build on existing bits, the name of this release will be BizTalk Server 2006 R3. Refreshing the bits (instead of applying a large Service Pack) provides for the best update experience. You should expect to see a CTP of BizTalk Server 2006 R3 later this year (with an RTM planned in H1 CY09).

http://blogs.msdn.com/stevemar/archive/2008/04/23/biztalk-server-platform-updates.aspx

Extract the iteration from a repeating loop designated by a certain qualifier

Eric Stott — Fri, 18 Apr 2008 13:47:28 GMT

A while back I posted a solution that helped me extract the value from a repeating loop designated by a qualifier. I've seen this come up many times with EDI, either you're extracting a ST or ship-to information or a REF reference information. Although my previous solution worked it involved adding 4 functiods and changing data within the scripting functiod depending on the Qualifier. I've simplified it to only require 3 functoids and no need for adjusting the script inside of the functoid.

Here's an example of what your 3 functoid would look like.

1. Add a "^" before and after the Qualifier

2. The scope of the Cumulative Concatenate Functoid should be 1.

3. The Scripting Functoid Input will be the Qualifier you're wanting to find and the output from the Cumulative Concatenate functoid.

4. Finally Insert the following code into the Scripting Functoid (Inline Visual Basic .NET)

Public Function RepeatingLoopWithQualifier_1(ByVal Qualifier As String, ByVal All_Qualifiers_and_Values_Concatonated As String) As String
        'All_Qualifiers_and_Values_Concatonated Expects a Commulative Concatonated String of all 
        'Qualifers preceded by a "^" and Values preceded by a "^" for example ^ST^912 Summer DR.^BT^4312 W. Powell 

        Dim QualifierLocation As Integer = All_Qualifiers_and_Values_Concatonated.IndexOf("^" + Qualifier + "^") 

        Dim QualifierandValueSplit As String() 

        'If Qualifier is found, find the corresponding Value, else Return ""
        If QualifierLocation >= 0 Then 

            QualifierandValueSplit = All_Qualifiers_and_Values_Concatonated.Substring(QualifierLocation + 1, All_Qualifiers_and_Values_Concatonated.Length - (QualifierLocation + 1)).Split("^")
            Return QualifierandValueSplit(1)
        Else
            Return ""
        End If
    End Function

Find the processes locking a .dll

Eric Stott — Thu, 17 Apr 2008 23:52:58 GMT

A lot of times working with multiple developers on one box you run into a situation where a process is locking your dll. The following command line might help.

tasklist /m <dllname>.dll

Execute SQL Query from within a C# function

Eric Stott — Thu, 17 Apr 2008 22:51:35 GMT

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;

Get the Original File Name from either the HIPAA or BaseEDI adapters

Eric Stott — Thu, 17 Apr 2008 22:15:20 GMT

One of the properties you'll quickly wish you had using either the BizTalk HIPAA or BaseEDI adapters is the original file name of the message with which you are dealing. We've all searched for BTS.ReceiveFileName to no avail.

Here I'm showing you that there is no BTS.ReceiveFileName:

We'll I've decided to build a C# class with two functions that will extract that for you easily.

The class I created has two static overloaded functions 1: GetBaseEDIOriginalFileNameFunction, and 2: GetHIPAAOriginalFileNameFunction.

Simply pass the <yourmessage(BTS.MessageID)> and (optionally) the Server where your HIPAA or BaseEDI databases are located into the function and you'll have your original filename.

Email me if you'd like a copy

First it was QCSI, Now Trizetto, Is Apax Partners next?

Eric Stott — Sat, 12 Apr 2008 05:26:57 GMT

Some official news:

http://www.247wallst.com/2008/04/trizetto-gets-a.html

Apax Partners is taking healthcare software company TriZetto Group Inc. private in a cash deal valued at roughly $1.4 billion, TriZetto said Friday, April 11.

In one of the few take-privates in recent months, shareholders will receive $22 per share in cash, a 25% premium to TriZetto's closing price Thursday of $17.67.

BlueCross BlueShield of Tennessee and Regence Group, both customers of TriZetto, are helping to fund the buyout with an undisclosed amount of equity. Regence is a combination of several BlueCross BlueShield operations in the U.S.

TriZetto's stock closed Friday up 15% to $20.39, near a 52-week high of $20.85. That gave it a market capitalization of $875 million. Its 52-week low is $14.52. The Newport Beach, Calif., company had $391 million of long-term debt and lease obligations on Dec. 31.

TriZetto provides software to health insurance plans and benefits administrators. Last year it booked $452 million of revenue, up 30% from 2006, a spokesman for the company said. Adjusted Ebitda, meanwhile, jumped 45%, from $67 million to $97 million, he said.

The bulk of its growth came from sales of its core administration software product, he said. Among other things, it helps manage healthcare claims and membership enrollment.

"A number of BlueCross and BlueShield plans signed up," he said, adding that these new customers drove most of the gains in 2007.

TriZetto's $1.4 billion take-private includes stock options and shares related to its outstanding convertible notes.

RBC Capital Markets is the sole underwriter for the debt financing, TriZetta said.

The U.S. healthcare system is disconnected, a TriZetto spokesman said, adding that the company's software helps it "work more smoothly" by linking consumers, providers, employers and brokers.

We see the confluence of healthcare and information technology as a key area of focus for strategic investments," Buddy Gumina, the head of U.S. healthcare at Apax Partners, said in a statement. TriZetto, he added, is poised to "drive positive changes in organized systems of healthcare, both in the U.S. and abroad."

The same day the TriZetto take-private was announced, Aurora Capital Group's Mitchell International Inc. said it would merge with CCC Information Services Inc., which is owned by Investcorp, in an all-stock deal valued at $1.4 billion. The drive to utilize technology better in the healthcare sector, meanwhile, drove Nuance Communications Inc.'s $400 million, April 8 deal to buy medical transcription software maker eScription Inc.

TriZetto said it does not anticipate any material changes in its product offerings, staffing or facility locations as a result of the buyout.

The company, founded in 1997 as MC Health Holdings, has made a number of acquisitions, including the 2007 purchase of Quality Care Solutions Inc., a deal valued at up to $146.2 million. In November 2005 it bought Boston-based CareKey Inc., an advanced-care management software maker, for $60 million with an add-on of up to $40 million if certain financial targets were met.

For financial advice on the take-private, Apax Partners turned to Deutsche Bank Securities Inc.'s Sarah Bradley, Ravi Sachdev and Raul Gutierrez.

For counsel, it tapped Kirkland & Ellis LLP's Kirk Radke, Kimberly Taylor and Susan Zachman.

UBS Investment Bank's Robert DiGia, James Brennan, Matt Kim and Brad White advised TriZetto, which relied on Gibson, Dunn & Crutcher LLP's Thomas Magill, Erik Greupner, Drew Bordages and Kevin Schubert for legal advice.

Co-investors BlueCross BlueShield of Tennessee and Regence were advised by Cain Brothers & Co. LLC.

The buyout by Apax is expected to close in four to six months, pending shareholder and regulatory approvals.

Having problems undeploying that pesky BizTalk Assembly?

Eric Stott — Wed, 19 Mar 2008 22:05:15 GMT

Have you ever received the following error message when trying to undeploy a BizTalk assembly?

"Some items in the removed assembly are still being used by items not defined in the same assembly, thus removal of the assembly failed."

Make sure that items in the assembly you are trying to remove fulfill the following conditions:
1. Pipelines, maps, and schemas are not being used by Send Ports or Receive Locations
2. Roles have no enlisted parties.

Here's a query used against the mgmt database for those of you on BizTalk 2004/2006 for finding the maps and pipelines that are still deployed that might be getting in your way of undeploying:

select    'RcvPort' PortType,
        r.nvcName Port,
        item.name MapName,assem.nvcName Assembly,
        indoc_docspec_name,
        outdoc_docspec_name 
from    bts_receiveport_transform rt 
        join bts_receiveport r on 
        rt.nReceivePortID = r.nID 
        join bt_mapspec ms on
        ms.id = rt.uidTransformGUID 
        join bts_assembly assem on 
        ms.assemblyid = assem.nID
        join bts_item item on
        ms.itemid = item.id
union
select    'SendPort' PortType, 
        r.nvcName Port, 
        item.name MapName, 
        assem.nvcName Assembly,
        indoc_docspec_name,
        outdoc_docspec_name 
from    bts_sendport_transform rt
        join bts_sendport r on
        rt.nSendPortID = r.nID 
        join bt_mapspec ms on 
        ms.id = rt.uidTransformGUID
        join bts_assembly assem on
        ms.assemblyid = assem.nID
        join bts_item item on
        ms.itemid = item.id
order by Assembly, PortType, Port 

select    'SendPort' Type,
        ReceivePortName = '',
        bts_sendport.nvcName [SendPort/ReceiveLocation],
        bts_pipeline.[name]PipelineName
from    bts_sendport 
        join bts_pipeline on
        bts_sendport.nSendPipelineID = bts_pipeline.[ID]
where    bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.XMLTransmit'
        AND  bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.PassThruTransmit'
union
select    'ReceiveLocation' Type,
        bts_receiveport.nvcName ReceivePortName,
        adm_receiveLocation.[Name] [SendPort/ReceiveLocation],
        bts_pipeline.[name]PipelineName
from    adm_ReceiveLocation
        join bts_pipeline on
        adm_receiveLocation.ReceivePipelineID = bts_pipeline.[ID]
        join bts_receiveport on
        adm_receivelocation.receiveportid = bts_receiveport.nID
where    bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.XMLReceive'
        AND  bts_pipeline.[name]<> 'Microsoft.BizTalk.DefaultPipelines.PassThruReceive'
order by Type, PipelineName

BizTalk Excel (XLS) pipeline component

Eric Stott — Mon, 17 Mar 2008 20:20:36 GMT

I've created a BizTalk Excel File decoder pipeline. It accepts Excel 2003-2007 .xls files. It will parse each worksheet into a child node under the root node you specify.

Here are the Pipeline Component Properties.

Here is an example XLS file.

The resultant XML file creates the Root Node "SHEETS" as specified in the pipeline properties. The children of "SHEETS" are the worksheet names listed in alphabetical order. I dropped 1200 Excel spreadsheets into a receive location and BizTalk parsed each perfectly in less than 10 seconds.

<Sheets xmlns="http://your.name.space.here">
  <First>
    <RowData>
      <Column1>row1column1</Column1>
      <Column2>row1column2</Column2>
    </RowData>
    <RowData>
      <Column1>row2column1</Column1>
      <Column2>row2column2</Column2>
    </RowData>
    <RowData>
      <Column1>row3column1</Column1>
      <Column2>row3column2</Column2>
    </RowData>
  </First>
  <Fourth>
    <RowData>
      <Column1>Fourth</Column1>
    </RowData>
    <RowData />
  </Fourth>
  <Second>
    <RowData>
      <Column1>Second</Column1>
    </RowData>
    <RowData />
  </Second>
  <Third>
    <RowData>
      <Column1>Third</Column1>
    </RowData>
    <RowData />
  </Third>
</Sheets>

To learn more about obtaining this Pipeline Component, go here.

Internet Explorer immediately closes when trying to open XML documents

Eric Stott — Mon, 17 Mar 2008 18:55:10 GMT

Something recently changed on a development server that disabled the ability for IE to open xml documents.

I resolved it by following these steps:

1. From start menu select run

2. Type 'regedit' and press enter. This will bring registry editor.

3. From the regedit menu select Edit and then Find

4. Type "HKEY_CLASSES_ROOT\Mime\Database\Content Type\text/xml" in the 'Find What'

5. Regedit will find the content that is related to that entry

6. Verify your classid CLSID={48123BC4-99D9-11D1-A6B3-00C04FD91555} ?

7. Verify your Extension=.xml ?

8. Verify your Encoding=hex:08,00,00,00 ?

If all those values are correct, but still not working, do the following:

1. From start menu select run
2. Type 'regsvr32 msxml3.dll'
3. Click OK on the dialog box.

Xref Information further explained

Eric Stott — Tue, 04 Mar 2008 17:21:43 GMT

A friend asked a few questions about the Xref functiods that I thought everyone (including myself) could benefit from

Can we have many application [sic] using Cross-referencing functoid and pointing to the same database? Yes, this is the beauty of the Xref functionality, you can create multiple Application Types, and application type would be for inbound purchase orders for example, and you can have another application type for outbound invoices for example. Even to a greater extent, you can have application instances for each application type, so you can have a partner specific application instance of the inbound purchase order, and a partner specific application instance for outbound invoices. The ListOfAppType.xml would look like this:

<?xml version="1.0" encoding="utf-8"?>
<listOfAppType>
    <appType>
        <name>Inbound Purchase Order</name>
    </appType>
    <appType>
        <name>Outbound Invoice</name>
    </appType>
</listOfAppType>

And here is the example of how the ListofAppInstance.xml would look:

<?xml version="1.0" encoding="utf-8"?>
<listOfAppInstance>
    <appInstance>
        <instance>Partner 1</instance>
        <type>Inbound Purchase Order</type>
    </appInstance>
    <appInstance>
        <instance>Partner 2</instance>
        <type>Inbound Purchase Order</type>
    </appInstance>
    <appInstance>
        <instance>Partner 1</instance>
        <type>Outbound Invoice</type>
    </appInstance>
    <appInstance>
        <instance>Partner 2</instance>
        <type>Outbound Invoice</type>
    </appInstance>
</listOfAppInstance>

Here is what it looks like within the tool

When is purge data script useful? There is a stored procedure that is called xref_Cleanup, this is a stored procedure that brings back the various tables to factory settings. Only use this when you want to clear out everything. There is currently no stored procedure nor out of the box way to clear out portions of the data. I actually opened a ticket because I could not load data after I truncated all of the data in the various tables because I could no longer import data into the tables anymore. Truncating the tables is not the way to clear out the tables, you need to run this stored procedure. For your information, here is the stored procedure to see what it does:
```
CREATE PROC [dbo].[xref_Cleanup]
AS
TRUNCATE TABLE [dbo].[xref_AppInstance]
TRUNCATE TABLE [dbo].[xref_AppType]
TRUNCATE TABLE [dbo].[xref_IDXRef]
INSERT INTO [dbo].[xref_IDXRef] (idXRef)
VALUES (N'')
TRUNCATE TABLE [dbo].[xref_IDXRefData]
TRUNCATE TABLE [dbo].[xref_MessageArgument]
TRUNCATE TABLE [dbo].[xref_MessageDef]
TRUNCATE TABLE [dbo].[xref_MessageText]
TRUNCATE TABLE [dbo].[xref_ValueXRef]
INSERT INTO [xref_ValueXRef] (valueXRefName)
VALUES (N'')
TRUNCATE TABLE [dbo].[xref_ValueXRefData]
```