TopXML Oracle Adapter Dynamic "like" Send Port

A colleague and I recently ran into a situation where we needed to determine the Oracle Connection string at runtime. This may sound like an odd requirement, but the 3rd party application that populates this database has been configured for redundancy in an a-typical fashion. It has not been set up in a fashion like a SQL Cluster...that would be too easy :-). So instead of letting an infrastructure component(like Windows Clustering) determine which instance is the "Primary", their software performs this function.

I have set up a POC that will walk you through this configuration.

The first thing that you are going to want to do is add a reference to your project. The DLL that you want to include is "BTSUtils.Adapters.Databases.Properties.dll". You can find this file in your \BizTalk Utilities\Database Adapter\Bin.

This will allow you to update the Connectionstring context property.

Below is an image of my orchestration. The orchestration doesn't do anything all that spectacular. The majority of the orchestration is for demonstration purposes.

Basically, a file is passed in that has data which will be used as part of the Oracle Request. So a simple map will take the value from the incoming document and map it to the Oracle Request.

Once, we have the Oracle Request document, we need to update the context property as part of the Message Assignment Shape. I have updated the
BTSUtils.Adapters.Databases.Properties.Transmit.Connectionstring and assigned a new value.

So obviously hard coding the connection string in your orchestration is not a good idea, but remember this is just some POC type code. In our requirements, we need to be able to connect to one of two data sources, but have to determine this at run time. Some options for storing these connections strings may be the BTSNTSvc.exe.config(clear text warning!) or in the SSODB(secure) and extracting the values via the SSO API.

Once this property is set, I submit the Oracle Request message and wait for a Response. The Expression shape in my orchestration, is simply a trace statement. I am interested in knowing how may nodes are returned as a result of my query.

After this log point, I simply drop the results of the query to a folder.

So if I modify the connection string inside of the Orchestration, then what does the Send Port configuration look like?

Interestingly enough, you do not have to populate the "Connection String" property with anything meaningful what so ever. You do need to provide it with a value in order to successfully complete the configuration though. I have decided to populate this property with the associated Macro. This way an Ops guy can look at it and figure out that the value is populated elsewhere.

Microsoft MVP Awarded: Windows Server System - BizTalk Server

I was recently informed that I have received MVP status from Microsoft. It is truly an honor to be recognized by Microsoft for this award. I look forward to the MVP experience and meeting other MVPs at Microsoft events such as the MVP Global Summit.

If you would like to see my MVP profile, you may see it here.

WMI Scripting for BizTalk Operations Tasks

While reading a BizTalk Forum on MSDN, I came across a post asking for guidance on detecting when a Receive Location is disabled that did not include MOM. I knew that a lot of information is retrievable via WMI, but I never investigated it much because we use MOM at the organization that I am with.

So I started to dig into WMI some more and came across this post that describes how you can added the managed WMI classes to your .Net application(Console/Winform/Service etc). http://geekswithblogs.net/gwiele/archive/2005/03/16/26469.aspx

I looked for a class that could help out with a disabled receive location and found this class: MSBTS_ReceiveLocation. When I selected "Generate Managed Class" a file was added to my solution called: ROOT.MicrosoftBizTalkServer.MSBTS_ReceiveLocation.cs
With this class added to my solution, detecting whether or not the receive location was enabled was a breeze:

WMITest.ROOT.MICROSOFTBIZTALKSERVER.ReceiveLocation rl =new WMITest.ROOT.MICROSOFTBIZTALKSERVER.ReceiveLocation("yourmanagementdb", "yourservername", "receivelocationname","receiveportname");

if (rl.IsDisabled)
{
rl.Enable();
}

Another Class that I can see some value in using is the MSBTS_HostInstance class. This took me a few minutes to figure out. In order to create an object instance of one of Host Intances run the following code:

WMITest.ROOT.MICROSOFTBIZTALKSERVER.HostInstance hi =new WMITest.ROOT.MICROSOFTBIZTALKSERVER.HostInstance("yourmanagementdb", "servername", "Microsoft BizTalk Server Hostinstancename servername");

if (hi.ServiceState == WMITest.ROOT.MICROSOFTBIZTALKSERVER.HostInstance.ServiceStateValues.Stopped)
{
hi.Start();
}

The last parameter when instantiating the object threw me for a loop. In order to figure exactly what the WMI call was expecting, I decided to check out the BizTalkMgmtDb to figure out what the "internal" name was. I found this value in the adm_HostInstance table in the Name column.

Overall it was a good experience playing with some of this WMI. As time permits I plan on doing some additional investigation.

BizTalk Delivery Notification

I have recently had some requirements that required me to use the BizTalk Delivery Notification feature of Send Ports. In case you didn't know, Delivery Notification "Gets or sets the field to request that the adapter will send delivery notification back, whether transmission is successful. " source

Basically, what I am trying to do is ensure that my message is sent to it's destination prior to the rest of the orchestration executing. If the message is not sent successfully, I want the orchestration to stop processing so that an "Ops guy" can take a look into the situation without losing the message.

My Requirements

I need to deliver a file to a Unix system where it will be consumed by our ERP system. Since some of the files may be large, it may take seconds or even minutes for the file to be completely written. Our ERP system essentially needs to know when it is "safe" to consume the the file. Once the data file, from here on in I will refer to it as the "work" file as it needs to be delivered to a "work" folder, is written a "signal" file is to be written to the "sig" folder. This is the ERP's trigger to pick up the work file. When writing the sig file, we use the same name as the work file, except we append ".sig" onto the end. This way the ERP knows which file to pick up in the work folder.

So what I need to do is ensure that the "work" file has been successfully written before the "sig" file. Otherwise our ERP may go looking for a "work" file that does not exist.

Solution

I have created a POC to demonstrate how to utilize Delivery Notification. For this sample I am using local file drops, but I have tested this with an FTP adapter as well.


So here is my orchestration. I have a standard receive shape followed by a Scope shape that has Transaction = None, Synchronized=True. To find out more info on Scope shapes click here

Inside of this scope, I have a log point and Send Shape that is connected to a Logical Send port. Within the Send Port, I have specified the "Delivery Notification" = Transmitted.

I have added an Exception handler to the scope shape that is configured to catch Microsoft.XLANGs.BaseTypes.DeliveryFailureException exceptions. When you enable Delivery Notification the orchestration will wait for an ACK/NACK to be returned when the message is sent. If an ACK is returned, the orchestration will complete the scope shape and continue processing. If a NACK is sent back from the engine then the orchestration will raise a Microsoft.XLANGs.BaseTypes.DeliveryFailureException which is why we need to catch this type of exception.

I have added a Suspend Orchestration shape to ensure that I can resume this orchestration instance in the event of a failure.

Outside the scope shape, I have another log point and I construct my sig file. There is not much to the sig file. It basically contains a timestamp. I do not have Delivery Notification enable for this logical send port as I will just rely upon the messaging engine's "default" capabilities for writing this file.

Testing

Under normal circumstances, the sample runs as expected. A file is picked up from the receive location where BizTalk tries to write the work file to the work folder and the sig file to the sig folder.

In the event that the work folder does not exist or the work file already exists, the sig file will not be written. In this test run, I have left a copy of the same file in the work folder. When the orchestration executes, I find 2 suspended instances in my BizTalk Admin Console. Note that for this test I have my Send port's Retry Count and Retry Interval set to 0 just to speed up the testing process. You can alter these values and the behaviour remains the same; the work file will be retried as many times as configured before it gets to this state.

At this point, in my production environment, I would receive a MOM Alert as BizTalk has logged an error in the Event Viewer. This would allow for some investigation into the problem. In my sample scenario I just remove the original file from this folder and am free to resume the Suspended Message Instance. Once resumed, the work file is written as it should be. In order to get the sig file written, I must now resume the Orchestration instance. Upon resuming this instance, the sig file is written to disk.

The order of these two steps is critical. If you resume the orchestration instance prior to the message instance, the sig file would be written prior to the work file. This is the scenario that we are trying to avoid by using Delivery Notification so it is important for operations staff to understand this concept.

Also be cautious about the potential performance impacts of enabling Delivery Notification. For more information related to performance, please check out Kevin Smith's blog

BizTalk Flat File Schema Wizard

I have found the Flat File Schema Wizard in BizTalk Server 2006 and BizTalk Server 2006 R2 to be extremely useful and figured that I would blog about it for anyone looking for an introduction to this feature.

Inside of BizTalk, the preferred message type is XML. If messages are in an XML format, BizTalk can do intelligent things with the messages. These "intelligent things" include routing the message based upon a promoted property, tracking elements in BAM or making logic decisions inside of an orchestration based on a particular value of a node.

Generally the way Flat Files are processed at Run-time are:

1. Flat file is received by adapter configured to watch the receive location's URI
2. A custom pipeline is required to disassemble Flat File into its XML equivalent
3. BizTalk Orchestration(if non-pure messaging scenario chosen) would then pick up this XML equivalent version of the flat file. The message would then move through the business process.
4. If/when BizTalk needed to send out a version of this XML message as a Flat file, a custom Send Pipeline would be required that would take the XML message and assemble the Flat File.
5. In the Send Port adapter, this custom Send Pipeline would need to be configured in order for the Flat File to be delivered to the end point.

I will now walk you through what is required at design time in order to enable the runtime to support the processing of Flat Files:

1. In your Schema and Maps project, "Add a New Item"

2. On the left hand side, select "Schema Files" and then select "Flat File Schema Wizard" from the right hand side. In the bottom centre of the wizard, provide a name for this Flat File Schema.

3. The Wizard based interface will now appear which will walk you through defining your Flat File structure. Click on the "Next" button to continue.

4. You will need an instance, or sample, of the flat file that you are about to create a schema for. Browse for this file, provide a "Record" name and update the "Target namespace" as required.

5. The wizard will then load up your instance file to further break it down. In this step, you need to define how records are differentiated. Depending upon the complexity of your flat file, your record definition may be different from mine.
For my sample, the structure of my record is as follows:

CustomerID,DiscountGroup,Address,City,EmailAddress,PhoneNumber{CR}{LF}. Since each record is contained within one line I want to select the entire first line.


6. I then need to indicate how the record is delimited. In my scenario it is based upon a Carriage Return/Line Feed. Something to note here is that my source file is based upon a Windows File structure. Unix indicates the end of a line differently than Windows.

7. In the next step, I am providing exactly how the child delimiter is defined. Since this is a Windows based file, I will select "{CR}{LF}" . Other options include {CR}, {LF}, {TAB}, {SPACE}, {0x1A}, {}, {.}, {;} or you can provide your own symbol.

8. So at this point, I have defined what a Record looks like but I have not broken down the various elements that make up a record. By default "Repeating record" is not selected, so you will want to pull down the "Element Type" drop down to select it. If you do not do this step, you will not have the ability to break down your records into individual elements/attributes.

Also, if you want, rename the parent node for each record, modify the "Element Name" field to your desired name.

9. Select "Next" to continue to the next screen where you will be able to further define how each record is broken up.

10. So here is the start of breaking down the record into individual elements. You want to ensure that the {CR}{LF} symbols are not selected and click "Next".

11. Here is where you tell BizTalk how to parse the record. The two options are by delimiter symbol or relative. Since I am using a CSV file, I selected "By delimiter symbol". If you have a fixed width file, then you would want to select "By relative positions".

12. By default, the comma symbol is populated in the Child delimiter drop down. If you use a different like a pipe '' or a period '.' you can change this here.

13. In this screen, you are able to define each of the Elements/Attributes for your data. Much like any XSD, you are able to define the data types as you see fit. Also note, in the far right hand column, BizTalk is loading the contents of you sample row into the wizard for you to verify whether BizTalk has parsed it correctly.

14 BizTalk will then show you a break down of what your XML tree will look like. Once you click Finish the Schema will be available to you in your BizTalk solution.

15 You now have a Flat File Schema for use within BizTalk!

Since the Disassembling and Assembling of Flat Files occur within pipelines, having this Flat File schema will not do us much good until we create the pipelines to provide the conversion between Flat Files and XML.

I will now walk you through the process of creating a pipeline that will use the Flat File Schema that we just created.

In the sample project that I am using, I have 1 "master" solution and 4 projects that belong to the solution. I have broken it down based upon a popular project structure:

• MapsAndSchemas
• Orchestrations
• Pipelines
• Helper (.Net Assembly used for any helper/utility methods)

So within my Pipelines project, I need to add a reference to my Schemas project. This will allow me to use this schema in my Disassembly/Assembly stages of my pipeline.

1. Add a "New Item" to your Pipeline project(or applicable project)

2. On the left portion of the dialog box, select "Pipeline Files", In the top centre of the dialog box, select "Receive Pipeline" and then provide a meaningful name in the bottom of the dialog box.

3. The Pipeline component designer will load. You will then want to drag a "Flat file disassembler" control from your toolbox and release it in the "Disassemble" stage.

4. With the "Flat file disassembler" control selected, select your Flat File schema that you previously created from the "Document schema" drop down.

5. You would follow the same steps when creating your Send pipeline. The difference being that you would be dropping a "Flat file assembler" control onto the "Assemble" stage in the pipeline. You would select the same Flat File schema from the "Document schema" drop down.

Once you have deployed your solution, you will want to ensure that you select your custom pipelines in the Receive Locations/Send Ports that you expect to receive/send these flat files.

n-software ftp adapter UPDATE

We have received a new build from n-software which included some enhancements related to the FTP context properties. I previously explained how any properties that were set via code would override any of the configured properties in the static send port. To see the original post check it here: http://kentweare.blogspot.com/2007/06/n-software-ftp-adapter.html

As of build 2.1.2729, you can still set these properties in your code. The difference now is that you can specify these properties as macros in your send port configuration(inside of BizTalk Admin).

So if you populate the msgOut(nsoftware.BizTalk.FTP.User) property in your code, you would now configure the static send port and specify the user as %User%.

You would follow the same pattern for any other context properties that you populate inside of your code such as msgOut(nsoftware.BizTalk.FTP.FTPServer) . In this case the macro in BizTalk Admin would be %FTPServer%.

BizTalk BAM Archiving Explained

Another great post by Richard Seroter regarding BAM and the "OnlineWindowTimeLength" field. This field is used to determine how long data that resides in the BAMPrimaryImport views remains "live" before it is archived.

http://seroter.wordpress.com/2007/06/22/biztalk-bam-data-archiving-explained/