SQL Server Adapters for BizTalk

Ok, so in my recent project I have multiple calls to make to a sql server's database. I ran into a problem calling the same database, but to get around that you simply add a unique ID to the end of the URI. It then would look something like: SQL://localhost/database_name/uniqueID

I got that information from the MSDN website.

Some FYI and Tips:

1) Have SQL Server do most of the work, with stored procedures when it comes to a large amount of updating.
2) Use some kind of status update when grabing data from a table to prevent multiple pickups with BizTalk. Use something like a date field or some type of varchar naming.
3) Use "select top 1" to make the document only have one root element.
4) SQL Adapters are limited to 60 messages a minute, unless you poll while data is found.
5) Try to keep the message size to under 1MB.
6) Save Tran and Rollback Tran will cause an error in the SQL Adapter
7) The generated root element is made in the adapter, not the store procedure.

Some tips on BizTalk 2004 to remember.

So here is a list of things that solved some of my errors that I have been having in developing a
BizTalk 2004 application.

1) I was having some trouble in developing maps for 2 of them. I then decided to use an external XSLT instead. I started getting un inforormative errors like unexpected ":" and missing ";" in the btm mapping on line 8 and 9. These errors of course made no sense, since there were no line 8 or 9. I knew the problem had to be in the XSLT. The probelm ended up being that I had to get rid of the Version = 1.0 attribute and a var declaration that I had in the xml:stylesheet declaration. This is what the line should of been:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:userCSharp="urn:userCSharp"
exclude-result-prefixes="msxsl userCSharp">

<xsl:output method="xml"
omit-xml-declaration="yes" /> <xsl:template match="/">

2) When writing scripts that use that same functions over and over again, then make a external assemblies. I also made the assembly version hard coded, so that when I change the scripts I don't have to worry about resetting the maps.

BTS 2002 to BTS 2004 Migration: Error about a user('null')

I was having trouble using VS 2003 Migration wizard to authenicate to a remote server. Of course I had to VPN into the domain. The problem I was getting after entering the server and database name, was an error stating that I could not connect because the username was null. The VS was suppose to use my credential for the connection. Solving the problem was done by going to the Control Panel > Stored User Names and Passwords. Add a connection with my username@domain and password. This got me in.

BizTalk Questions: Migrating BTS 2002 to BTS 2004

Well, I'm starting to read a article on migrating BT2002 to BT2004 and seen these questions and answers.

What is a promoted property?

Property promotion enables you to flag schema nodes at design time for promotion to the MessageBox at run time. In addition to any custom metadata you explicitly define by promoting properties, BizTalk Server 2004 has several pre-existing properties that it tracks by default. These pre-existing properties are not defined by instance schemas.

What is a binding file?

Binding files are shortcuts so that you can bind once using the BizTalk Explorer, export that binding, and then import it when you need to assign the binding subsequent times. For example, if you install and deploy your development environment and save a binding file, when it is time to go into production, you can script the Deployment Wizard and specify the binding file to use to create the bindings for the ports. For messaging migration, the Migration Wizard only generates one binding file for the send or receive locations in a solution.

Can I have multiple schemas with the same root node?

No. The messaging engine relies on the root node to resolve an XML instance to a schema at run time. (This is possible in BizTalk Server 2002.)

Can I add an orchestration within my migration project?

Yes, it is possible, but not recommended because you have to manually create a send/receive port to/from an orchestration, thereby complicating the migration of your messaging solution.

Is it possible to create an orchestration that receives a non-XML message (a binary large object (BLOB) for instance) through a pipeline with a custom disassembler and promote properties in the blob?

You can pass type-less [aka: un-type] messages through orchestrations by declaring them as an XmlDocument. If you want to retrieve their data, you need to pass them to a .NET object as XLANGPart/XLANGMessage and call part.RetrieveAs(typeof(Stream) ); by using part.LoadFrom( myStream ), you can also construct non-XML messages in an orchestration.

How do I create a dynamic send port?

You can create a dynamic send port either using BizTalk Explorer or within an orchestration:

BizTalk Explorer – Create the port using BizTalk Explorer, and then assign values to the transport properties in BizTalk Editor using the global property schema. You can then select the appropriate schema from references. By default, BizTalk Server 2004 references Microsoft.BizTalk GlobalPropertySchema. Values to the transport properties come from your document. You need to promote elements from your schema using the global property schema.

Orchestration – You can create dynamic ports in an orchestration, and, after you deploy it, the port is accessible from BizTalk Explorer. In the orchestration, you need to assign the address (transport) using an Expression shape.

Will the Migration Wizard migrate custom script functoids?

No. Any maps that are migrated that contain custom script functoids need to be opened and functoids re-coded.

Will the Migration Wizard migrate my queue receive functions?

Yes. BizTalk Server supports all types of receive functions including BizTalk message queuing (MSMQ) file receive. BizTalk Server supports the conventional HTTP receive function, but not the HTTP file receive function because it has no meaningful use in the product.

Will the Migration Wizard migrate my pre-processor?

No. You must update your project by manually migrating any components such as pre-processors not migrated by the Migration Wizard.

Will the Migration Wizard migrate my channel routing and filter configurations?

No, with one exception. BizTalk Server 2004 automatically migrates routing and filtering configurations for channels that use dynamic routing or filtering. Custom routing done in a channel requires that you promote the properties and create the filters.

Will the Migration Wizard migrate orchestrations?

No. The Migration Wizard only migrates the following items.

• BTS 2002 document definitions to BTS 2004 schemas


• BTS 2002 maps to BTS 2004 maps


• BTS 2002 receive functions to BTS 2004 receive locations


• BTS 2002 ports and channels to BTS 2004 ports and pipelines


• BTS 2002 port groups (distribution lists) to BTS 2004 send port groups

The content in this section is provided by Ben Goeltz, Brandon Gross, and Stephen Roger of Equarius, Inc. For more information about Equarius, Inc., see http://go.microsoft.com/fwlink/?LinkId=26941, published in September 2004.