SQL Connection Information for BizTalk Adapters

by | Jan 3, 2009 | BizTalk Community Blogs via Syndication

I created a SQL Bulk load adapter as the standard SQL Adapter casts the stream to a string and runs out of memory for large datasets.

While developing the Bulk Load adapter, I need to specify the connection information. I was about to implement a custom interface, I took the lead from Tomas Restrepo’s Null Adapter. I was having some problems because I was developing on a 64bit box, and then discovered this information about implementing the adapter on 64 bit machines.

I thought that there must be an easier way to do this!

There is an undocumented schema annotation that allows to add connection information.

<xs:element name="ConnectionString" type="xs:string">
  <xs:annotation>
     <xs:appinfo>
         <baf:designer>
             <baf:displayname _locID="ConnectionStringName">Edit this field in the resource file</baf:displayname>
             <baf:description _locID="ConnectionStringDesc">Edit this field in the resource file</baf:description>
             <baf:editor assembly="%BTSROOT%\Microsoft.BizTalk.Adapter.SQLAdmin.dll">Microsoft.BizTalk.Adapter.SQLAdmin.DatalinkUITypeEditor</baf:editor>
             <baf:converter assembly="%BTSROOT%\Microsoft.BizTalk.Adapter.SQLAdmin.dll">Microsoft.BizTalk.Adapter.SQLAdmin.DatalinkTypeConverter</baf:converter>
             <baf:category _locID="DatabaseGrp">Edit this field in the resource file</baf:category>
          </baf:designer>
      </xs:appinfo>
   </xs:annotation>
</xs:element>

Here is the adapter interface:

Also, I wanted to show the database information, how you have to do it in C# is the following:

OleDbConnection connection = new OleDbConnection(valueOfConnection);
if (connection.DataSource.Length != 0) uri.InnerText = "SQL://" + connection.DataSource + "/" + connection.Database;
else uri.InnerText = "SQL://" + connection.DataSource + "/";

Which shows the following:

EDI Warehouse

by | Jan 3, 2009 | BizTalk Community Blogs via Syndication

In an effort to standardize the consumption of EDI documents, what I have come across is the same pattern.

  1. Create flat file schema
  2. Bring in and EDI Transaction
  3. Create the mapping to a fixed length flat file.
  4. Deploy
  5. Start Testing

This is pretty standard, and there is nothing wrong with this approach, it is pretty easy, and relatively quick to implement.The problem is that when the backend changes, the business now wants different data, or they come back and inform that something is missing. The normal request is “we need to be getting field X, can you run the past {month/year} data?”

So what that means is the following pattern must me followed:

  1. Modify flat file schema
  2. Modify existing map
  3. Undeploy map project
  4. Redeploy modified flat file project
  5. Redeploy modified mapping project
  6. Apply bindings
  7. Run all files through updated process

This becomes very tedious when either the output is not clearly defined, or there are a lot of transactions to process.

What we have created is a EDI warehouse that stores all EDI data. Below is the data flow: (click to enlarge)

Once the data is loaded with it’s subsequent primary keys, it waits until the next transaction in the interchange is processed. Once the transactions have been processed, it calls a stored procedure that will extract the data to whatever the business needs.

The nice part of this design, is that when the business needs different data, simply changing the query is all that is now needed. No more code changes, redeploys, etc.

Items that come with the EDI Warehouse package:

  • Because the SQL Adapter runs out of memory for large transactions, a custom Bulk Load adapter is included
  • The following transactions are included: 820, 850, and 856 which include the following:
  • Table definitions for each transaction, map into warehouse, mapping schema for load process

Any ANSI X12 transaction can be purchased for the prices on the Products/Services page.

2008 : Year in Review

by | Jan 2, 2009 | BizTalk Community Blogs via Syndication

As 2009 starts, I thought I’d take a quick gander at the 2008 posts I enjoyed writing the most, and a few of my favorite (non-technical) blogs that I discovered this year.
Early last year I embarked on a 9-part series of articles about how BizTalk and WCF integrate.  I learned a lot in the process […]

I have been re-awarded MVP Status (for a 6th year)

by | Jan 1, 2009 | BizTalk Community Blogs via Syndication

I’m honored to announce that Microsoft has, for a 6th consecutive year, awarded me MVP status.

2008 was a turbulent year, that saw me achieve elite and ultra-elite status with multiple airlines and hotels, with far too many hotel nights and air miles. It feels like I probably spent more time away from home than I did at home.

As we start 2009, we have a lot of fear, uncertainty and change ahead of us. The global economic upheaval that has touched us all will lead to changes which I don’t think we can fully grasp yet, and in that change, will lie opportunities.

One thing is certain though. We as an industry are on an exciting ride. So many enabling technologies have matured to the point that we can use them now to construct systems that only a few short years ago were un-imaginable. I am so excited by the possibilities I see in Oslo/BizTalk/Azure/Dublin/WF/WCF. No matter what lies ahead, I plan to continue on that voyage, and blog about the trip we’re all on. There will be lots to talk about…

Happy New Year!

Brian

Implementing the Resequencer Pattern with BizTalk 2009 and Frends

by | Jan 1, 2009 | BizTalk Community Blogs via Syndication

Article Source: http://geekswithblogs.net/michaelstephenson

I’ve recently been looking at a proof of concept of implementing the resequencer pattern in BizTalk 2009. When I’ve implemented this pattern in the past it usually required a lot of custom development to create a database to support the resequencing process. If you would like to see more on the theory of the resequencer pattern then refer to the following link: http://www.eaipatterns.com/Resequencer.html.

I’ve seen a few .net and BizTalk examples which discuss doing the resequencing in memory, but for my case I felt this would be a limiting factor as it would mean I could only use 1 machine to implement this on and there could be scalability issues. The aims of my proof of concept were as follows:

  • To create a demonstration of the resequencer pattern that would be scalable to handle lots of messages in BizTalk
  • Be able to extend this relatively easily to handle resequencing messages from multiple sources. For example if you had many partners pushing in messages and you only had to resequence messages from each partner separately
  • Minimise custom development and maintenance requirements to support this. I didn’t want to have any custom databases in my solution.
  • Try to make the resequencing pattern implementation as reusable as possible

Before I got into the details of this I was already familiar with the Frends product which I have discussed how it could be used to compliment BizTalk solutions in previous articles. My thought was that I might be able to use the configuration store part of Frends to help me to manage the sequencing in my POC. The rest of this article will discuss how my POC works.

My POC Environment

The environment on which I have build my POC is a Windows 2008 virtual PC running the CTP of BizTalk 2009 and SQL Server 2008. I have also installed the trial version of Frends.

Configuring Frends

I would need to configure a “Connection Point” in Frends. A connection point is basically an instance of some configuration data. You can set these up through the Frends Management Console where you will provide a schema which will define your configuration data, and then in Frends you specify values for the schema elements. To do this I took the following actions:

  1. Create Scheduling Configuration Schema

In my Visual Studio solution I created the below schema. In this schema I have defined an element to hold the sequence number of the last message processed, and an element to hold the time the last message was processed.

  1. Create Connection Point

In the Frends Management Console I would choose to add a new Connection Point like in the below picture.

On the add new Connection Point screen you need to supply the name of the connection point and choose a schema which will represent the configuration data. I have chosen the schema I created above and you can see in the below diagram that it will create a designer to allow you to supply values for the configuration. To begin with I will leave the time element blank, but I will supply the value zero to be the last processed message as it will indicate the start of the sequence. One interesting thing about the connection point is that you can have multiple connection points with the same name if you supply a different Party Name for each. This lets you correlate a specific connection point instance to a party you may be dealing with.

In the picture below you can see my configured Connection Point.

Setting up my BizTalk Solution

In setting up my BizTalk solution I have two projects. The first project will contain my BizTalk artefacts and the second will contain some utility components. These are shown in the below picture.

The key points of the solution are described in the below table:

Artefact

Description

InputSchema.xsd

This is the message schema that will be input into the process

SequencedOrchestration.odx

This is an orchestration which will start an instance for each message added, and will not continue until it is the next message to process.

SequencingFacade.cs

This class will implement a fa%u00e7ade pattern to the Frends system

The input message will look like the below picture. It simply is a message containing the number of the message.

For each message that is received an instance of the SequencedOrchestration will start. The below picture shows the orchestration and I have highlighted some of the key points to discuss:

  1. The loop will continue until the variable which indicates this instance is the next in sequence is set to true
  2. This expression shape will call the SequencingFacade to work out if this instance is next
  3. If this instance is not next then the instance will delay for a while
  4. When this instance is next the loop exists and I will create the message to send out. I also set the File Adapter context property do I can out put a file name which is the same as the sequence number
  5. This expression shape will call the SequencingFacade to update the last processed sequence number

The Sequencing Fa%u00e7ade

In the SequencingFacade class the first method to discuss is the AmINext method. This method will call Frends to obtain the connection point information. Frends makes the Connection Point subsystem available as a web service using WCF. Once I have called the web service I can get the configuration data from the response as a string of XML. Because I’m using BizTalk 2009 and can compile the .net 3.5 features in my solution I have chosen to use Linq to XML to parse this XML to get the sequencing details.

I can then implement some simple logic to work out if the instance is the next in sequence which you can see in the below picture.

The next method in the SequencingFacade is the UpdateSequence method. In the below picture you can see I get the sequence information from Frends and then use Linq to XML to update the xml to indicate a message has been processed. I finally call the Frends web service to update the connection point.

Walk Through

Now that we have reviewed the highlights of this implementation I will walk through a sample of how it works.

  1. We are currently at the start of the process and I have configured Frends that the last message processed was zero. The output folder contains no messages and there are no instances running in BizTalk.
  2. I add message with sequence 1 into the input folder. This message is collected and processed. Message 1 ends up in the output folder, and the Frends connection point is updated as in the picture below
  1. I now add message 3 to the folder. You will that this instance will be running in BizTalk and will keep checking Frends to see if its next, and will keep using the Delay shape between checks.
  1. I will now add message 4 to the folder. You will see this will also continue waiting like message 3.
  1. I finally will add message 2 to the input folder. You will see that it will process first and complete, then message 3 and 4 will follow it out in order. You will be able to see all messages in the output folder and finally Frends will know that the last message processed was 4
  1. I also has debugview running during the walkthrough and you can see from some of the trace messages I recorded that the messages were processed as expected

Summary

My proof of concept proved to be quite successful. I was able to setup this demonstration very quickly and it did everything I required. I think I have been able to show an implementation for the resequencer pattern which will be quite scalable and able to handle a lot of messages by taking advantage of the BizTalk scalability model and its ability to process lots of orchestrations at the same time. Some of my other thoughts about this pattern which may help move this to a real world implementation are as follows:

  • I would be looking to use the party name bit of Frends to help me handle messages coming from different sources
  • I would probably look to use something like NCache or Velocity to do a distributed caching of the responses from Frends if I was going to have a high load scenario. I need to have the permenant storage which Frends provides but by caching the sequence number and updating the cache when a process is complete I would not need to call the Frends web services very often. Also using one of the distributed caching solution would mean I can synchronise the cache across multiple machines within my BizTalk group
  • I have coded the interaction with Frends in C#, but it could equally be done in an orchestration by calling the services with a send port etc. The Frends service would normally be running on your BizTalk machine anyway so I guess you could choose how you did this
  • I could probably implement the whole loop in a seperate orchestration where you would pass in the name of the connection point (and optional party) and your sequence numberto this orchestration which would then interact with Frends. This means you could potentially implement resequencing of any of your orchestrations by just calling this other orchestration (note this will mean you have more running instances)

If you would like to have a look at the sample code then it is available on box.net at the below link. (Note some companies block box.net so if you are unable to get the sample then let me know)

http://www.box.net/shared/ircluappxe

BizTalk 2009 Testing Features

by | Dec 31, 2008 | BizTalk Community Blogs via Syndication

Warning: This post is about a beta product. Details
might change before final release.

The beta of BizTalk Server 2009 brings a welcome new feature: Some basic support for
Unit Testing BizTalk artifacts; specifically for Pipelines, Maps and Schemas.

You can read the basics about how the new feature works in the product
documentation; there’s not too much yet, but it’s enough to get a good idea of
how you use it.

I’d like to now share a few thoughts on this feature. Note: This is just my personal
opinion from playing with the API for a bit.

The Good

The first really good part about this feature is the fact that it even exists at all.
Facilities for creating and automating tests of BizTalk artifacts has been a sorely
lacking area of the product.

While Visual Studio had the Test option for maps and schemas since BizTalk 2004, and
it was sort of useful, it required way too much manual intervention and there was
no way to decently drive it through automation, making it useless for either unit
or regression testing.

The second thing I like about the unit testing feature is that, in it’s current incarnation,
it’s not intrinsically tied to MSTest (i.e. the Visual Studio Team System stuff).
This means you can use it with NUnit, xUnit, or your favorite testing tool.

The third interesting thing about the API is that it is a very simple API. In most
cases it really consists of just a single method call. Here’s an example of testing
a schema:

string fin = @"c:\temp\test1_in.xml";
Schema1 schema = new Schema1();
Assert.IsTrue(schema.ValidateInstance(fin, OutputInstanceType.XML));

The Not-so-Good

Not everything is rosy in BizTalk-land. Personally, I was somewhat underwhelmed by
the implementation of the Unit Testing features available in beta 1; particularly
since some of it could be addressed with fairly minor adjustments.

API Issues

Let’s start with the API: I mentioned before that the new testing API was simple.
However, it’s actually too simple. For example, consider the small test for
a schema I presented above: You simply create an input file to test with, call ValidateInstance() and
check the result. Great, no?

Well, if the test succeeds, then yes, it’s just fine. If it fails, however, because
the input file does not pass validation, then, it turns out, it’s missing one extremely
important functionality: There’s no way to get any details about why the
schema validation failed [1].

This means that diagnosing a failed test involves, at the very least, changing your
project settings in VS to use the failed input file as input to the schema in VS,
use the Validate Instance option in the IDE and examine the results in the output
window, where fortunately you do get more detailed error information.

This is extremely sub-optimal and requires way too much manual work. Can you imagine
if a change in a schema broke tens of tests and having to do this for each and every
single one of those? Ouch!

Another place where I feel the current API is not as nice as it could be is that it’s
strictly file-based. That is, you need to create actual files on disk to represent
inputs to the tests and store the resulting outputs. This is somewhat convenient for
many scenarios, and matches the existing functionality in BizTalk pretty well. It
does, however, make it very inconvenient to deal with dynamically generated inputs
or inputs stored someplace else.

The classic example here, would be, resources. For example, I very much like to keep
my unit tests as independent from the environment and as self-hosting as possible.
One very nice way of doing this is to store necessary input files as embedded resources
on my testing assembly. I do this all the time with PipelineTesting and
it works great. However, since you can’t provide streams instead of file paths to
the BizTalk 2009 testing features, you’d need to first save the embedded resource
to a temporary file, which adds a bit of friction to the process.

Compilation Changes

My biggest gripe with the Unit Testing features, is that enabling them implies a change
in the build process, that changes the generated code for the BizTalk artifacts themselves.

When you enable the Unit Testing option in Project -> Properties -> Deployment,
the compilation process will change the generated CLR types so that they inherit from
the TestableXXX classes defined in the Microsoft.BizTalk.TestTools.dll assembly,
instead of the normal BizTalk artifact classes.

These new Testable base classes, in turn, derive from the original BizTalk classes,
so the change isn’t all that big. However, it does smell wrong. Could it
introduce any problems/bugs if you were to enable the testing features for all builds?
Hopefully no, but no way to know until you run into them.

Of course, you could just enable Unit Testing for debug builds, but then you can’t
run your unit tests on the release builds of your BizTalk assemblies. It also introduces
yet another asymmetry between development and deployment builds which, to be honest,
makes me somewhat nervous.

Conclusion

The new Unit Testing features in BizTalk Server 2009 are a sorely needed and welcome
feature, if somewhat lacking in the first beta.

Will it improve? On one hand, I’m somewhat optimistic that some improvements might
make an appearance in forthcoming builds. However, I’m also not getting my hopes up,
as MS is notable for trying to avoid significant API changes during after beta 1 hits
the street (BizTalk, unfortunately, doesn’t use the CTP model, where significant API
change requests are more likely to be considered).

[1] TestableMap.TestMap() does throw exceptions on failure instead of
a Boolean return value, but said exceptions might not contain detailed error information.

2008 coming to a close…..so Just how is BizTalk doing out there??? Gartner Reports

by | Dec 30, 2008 | BizTalk Community Blogs via Syndication

I recently came across a Gartner report talking about all things to do with ‘App Integration
with Back End Systems’ (in a nutshell… the report goes into detail)

The end result of several pages within this report is a graph (we like graphs 🙂 showing
Microsoft as a leader with a high ability to execute.

The Microsoft Technologies that fell under the microscope here are:

  1. BizTalk Server
  2. Windows Communication Foundation
  3. SQL Service Broker
  4. SQL Integration Services
  5. Team Foundation Suite
  6. Oslo + Azure

The graph looks as follows (snipped from the report):

 

Get the whole report HERE

Screencast: Configuring MEX endpoints

Screencast: Configuring MEX endpoints

by | Dec 30, 2008 | BizTalk Community Blogs via Syndication

In this short WCF screencast, you'll learn how to configure your WCF services with Metadata Exchange (MEX) endpoints – both over HTTP and TCP – allowing consumers to automatically retrieve metadata from your services at runtime.  Enjoy!

configuring-mex-endpoints-300 

Be sure to check out our growing collection of short screencasts on the Pluralsight screencast page.

Previous WCF Screencasts (RSS for all posts in the series)