Jul 312008

The Thomson system for processing UPS files uses pipe delimited flat files and the UPS generated files are comma delimited. Commas were being replaced with pipes manually and we developed a process that does this automatically. The important part of this was changing the matter field format from the user input to a standard.

The matter field was coming in using the following formats: 99999-01, 99999/01, or  99999.01. They needed to be in the format xxxxx.xxxxx. They would come in all numeric, or alpha-numeric. They were separated by either a (-), (/), or a (.) or a white space.

As an example a Matter Field would come in looking like this:

image or image   or image the output needed to be all numeric looking like this image (five numbers followed by a period and five more numbers and enclosed in pipes.)


If anything came in with something besides a period as a separator  we replaced it with a period. If the segment on the left came in with anything less than five numbers we padded it with zeros i.e. 01 -> 00001, anything more than five numbers we trimmed it to five.  If the second segment (right of the separator) came in with anything other than numbers i.e. 12345.02bigman, we trimmed the letters and padded the number with 0’s ie 12345.00002.

We also had to deal with fields being populated  with commas in the text ie. ,”America Town, USA”,. We set up our BizTalk Program to recognize the the quotes and output |”America Town, USA”|, essentially ignoring any problems with commas in text.

Jul 282008

I had previously created a pipeline to replace any string.  What I liked about it was its simplicity.  I could just put the string I wanted replaced, followed by a comma then the string I wanted as the replacement.  I wanted to expand that functionality to allow replacing commas and other characters including HEX characters.  Regex was my solution, so my new version allows you to replace any string with another using Regex expressions for .NET.  Just include as many replacements as you’d like separated by commas.


Here’s a sample Regex expression I included:

(\d)\x2C(\d),$1$2,»,HEX CHAR,FRANK,RIZZO


It replaces

1.  a digit surrounded comma with nothing ((\d)\x2C(\d),$1$2)

2.  a HEX character “»” with the words HEX CHAR (»,HEX CHAR)

3.  The word FRANK with RIZZO (FRANK,RIZZO)


Here is my input file:


Here is my output file:



Note to Self:  Don’t forget to put parentheses around the groups for which you are searching.  It allows the $n to work.


If you’d like a copy click here.

Jul 232008

I was given the task of connecting BizTalk to an Informix database. When the project was first kicked off, the client started exploring their options, one of the companies that they looked at was Attunity.

I told them that the Linked Server option was a much better approach, while offering significant cost savings!

So I started my research on how to get connected, and I came across a few pages of interest:

The first is IBM documentation page about how to set up the actual connection within Enterprise Manager.

When I first started connecting, I was getting this error:

The OLE DB provider Ifxoledbc for linked server INFORMIX_TEST reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider Ifxoledbc for linked server INFORMIX_TEST. OLE DB provider Ifxoledbc for linked server INFORMIX_TEST returned message EIX000: (-951) Incorrect password or user Administrator@::ffff         is not known on the database server.. (Microsoft SQL Server, Error: 7399)

After doing some more research I found additional articles that were of interest:

One that I thought might be a problem was the permissions for the temp folder as documented on KB814398 to deal with Error: 7399. I set the permissions as documented with the same results.

Another IBM’s documentation page about how to set up the linked server and the fact that you need to install the coledbp.sql on the Informix database so that the OLE DB can actually connect.

Here is what I had setup (of course the actual connectivity is different)


I had read an article about mapping between the local instance of SQL Server and the security account on a remote server, but did not try it.

I finally clicked on the security button and filled in the username and password


It worked!

You also have to go into the Provider list and in the properties of the Ifxoledbc click on the Allow inprocess


What I don’t quite understand is the relationship between the Provider string and the security context they are the same, but when would they be different?

Jul 232008

In generating an xml from a word document we were required to extract data from different formats. An example of each is below (the differences can be subtle sorry if its monotonous). The documents were generated by a software from White Hill Technologies  (now Skywire Software).













Jul 222008

We had to take a timecard that was in MS Word 2003 format and create XML.  An example of the word document is as follows.





The red box indicates text that was hidden in the document which I have made visible for clarity. The program reads through the document, extracts text from the fields we are interested in and then converts it to XML as follows:

<?xml version="1.0" encoding="utf-8"?> <ns0:file xmlns:ns0="http://schemas.stottis.com/thomson/3e/proforma/detail"> <record> <date>Jun 04 08</date> <narrative>Time Entry Narrative</narrative> <hours>5.00</hours> <timecard>3330372</timecard> </record> <record> <date>Jun 05 08</date> <narrative>Time Entry Narrative</narrative> <hours>2.00</hours> <timecard>3330371</timecard> </record> <record> <date>Jun 07 08</date> <narrative>Time Entry Narrative</narrative> <hours>5.00</hours> <timecard>3330370</timecard> </record> <record> <date>Jun 08 08</date> <narrative>Time Entry Narrative</narrative> <hours>3.00</hours> <timecard>3330369</timecard> </record> </ns0:file>