2006 - Page 127 of 138 - BizTalkGurus

BizTalk 2004: SQL Adapter and SQL Ports: User Interface: Review

by community-syndication | Mar 14, 2006 | BizTalk Community Blogs via Syndication

BizTalk 2004: SQL Adapter and SQL Ports: User Interface: Review

I’m considering only working with stored procedures, not SQL commands nor Updategrams. Generating the SQL port artifacts with “Add Adapter Wizard” is out of scope this article.

Two-root schemas

The SQL ports use the Two-root schemas, one root for Request and one for Response. We’ve got one root empty if we use the stored procedure without input parameters.

Why? The one reason I can see is the logical grouping two schemas working with one port. From one side it logically links those schemas; from other side it seriously complicates the issue. Now we have to use the multi-part message types, have to manually prepare them after the schema generating procedure. Such preparing includes copying the multi-part message types and the special port types, using these messages types, to our orchestration. In most cases we should rename this schema and copy it to other project. It is not a straightforward process.

Why there is not used the standard Request-Response object model with two separate schemas? In this case we should manually create in the orchestrations the ports working with such schemas. There are not the multi-part message types, not all error prone steps around.

Anyway, the SQL ports use the Two-root schemas.

One-way and two-way ports

The stored procedures always return the data. In the simplest case it is the integer value (by default 0 means Success). That means all SQL ports (working with stored procedures) should be two-way ports.

Let see, is it true or false.

Send ports

We can create one-way and two-way SQL Send ports.

(Pict. 1. One-Way Send Port Properties window)

(Pict. 2. Solicit-Response (Two-Way) Send Port Properties window)

As you can see the only difference is the two-way port can use different pipelines for send and receive ways when the one-way port use one pipeline for both directions.

Receive ports

For the SQL Receive ports we can create only one-way ports.

Note

Undercover we always work with two-way SQL ports (ports which work with stored procedures) no matter how they are called in BizTalk.

SQL Send ports

When we click the “secret” button [] in the URI field of the Send Port Properties window (Transport tab) we’ve got the “SQL Transport Properties” window.

(Pict. “SQL Transport Properties” window of the Send port)

SQL Receive ports

When we click the “secret” button [] in the “URI” field of the “Receive Port Properties” window (Transport tab) we’ve got the “SQL Transport Properties” window.

(Pict. 1 “SQL Transport Properties” window of the Receive port)

After configuring the “Connection String” parameter we’ve got the value of the “URI” field. It is compounded from a protocol name – “SQL://”, a server name – “localhost”, and a database name – “Court_Prod”.

There is another “secret” button [] in the “SQL Command” field.

(Pict. “Import Information from a generated schema” window)

Does the “Project” drop-down field hold the project names? No. It holds the names of the deployed assemblies and the public BizTalk assemblies like Microsoft.BizTalk.GlobalPropertySchemas or Microsoft.BizTalk.DefaultPipelines.

We should choose the schema. It should be the schema generated by “Add Adapter Wizard”.

The generating schemas for SQL port is out of scope this article, but the name of this wizard is the other naming odd of the BizTalk. This wizard does not add adapter. We are adding adapters from BizTalk Administrative Console. This wizard generates the objects (artifacts) for the SQL Port. In BizTalk 2006 it is called “Add Adapter Metadata Wizard” but anyway it adds metadata not for adapter but for the port. The adapter metadata should be the “default” data for all ports this adapter type not for the specific port.

After this we have the modified “SQL Transport Properties” window.

(Pict. 2 “SQL Transport Properties” window of the Receive port)

There are three more parameters: “Document Root Element Name”, Document Target Namespace”, and “SQL Command”.

Take in mind these three fields are Editable, you can change it by mistake. Why it so? If these fields fill up automatically why we need to change them by hand?

Submitting Xml Documents to BizTalk via SOAP

by stephen-w-thomas | Mar 14, 2006 | Downloads

This sample shows how to modify a BizTalk Web Service to submit Xml Documents into BizTalk and route them using direct binding. The key to routing the message submitted by the Web Service is to make sure the Receive Port uses the Xml Disassembler pipeline.

This sample will work with BizTalk 2006 and BizTalk 2006 R2.

Get more information from the original blog post on this topic: http://www.biztalkgurus.com/biztalk_server/biztalk_blogs/b/biztalk/archive/2006/03/14/how-to-use-soap-to-submitt-xml-documents-into-biztalk-server.aspx and http://www.biztalkgurus.com/biztalk_server/biztalk_blogs/b/biztalk/archive/2006/03/14/working-with-untyped-web-services-in-biztalk-server.aspx

Working With Untyped Web Services in BizTalk Server

by stephen-w-thomas | Mar 13, 2006 | Stephen's BizTalk and Integration Blog

Creating an Untyped Web Service will allow you to easily submit Xml Documents into BizTalk through the web service. Make sure you check out my previous post on this topic.

Create the Web Service

1. Run the Web Service Publishing Wizard

2. Select “Publish Schema as A Web Service”

3. Delete “WebService1”

4. Right-Click on BizTalkWebServce, select Add Web Service

5. Right-Click on WebService1, select Add web method, One-Way

6. Right-Click on Request, select Schema Type

7. Select any dll with a schema, select any schema

8. Select next and fill out any other required information

9. Make sure you select Create Receive Port otherwise you’ll have to do this manual

Edit the Web Service

1. Open the newly created web service

2. Edit the web method signature to look like this:

public void WebMethod1(System.Xml.XmlDocument part)

3. Look for the inParms line it should look like this:

Microsoft.BizTalk.WebServices.ServerProxy.ParamInfo[] inParamInfos

4. Change the TypeOf to be System.Xml.XmlDocument

These same steps should work for BizTalk 2004 as well.

How To Use SOAP to Submitt Xml Documents Into BizTalk Server

by stephen-w-thomas | Mar 13, 2006 | Stephen's BizTalk and Integration Blog

You probably know about untyped messages inside an Orchestration (that is, messages treated as Xml Documents rather than typed schema). Wouldn’t it be nice to have an untyped web service to submit messages into BizTalk?

How would this make life easier?

You could have one web service to receive all your different message types. Then, use direct binding and content based routing to get your messages to the correct destination.

This will not work for all scenarios. But, it can greatly increase your flexibility when submitting messages into BizTalk through a web service by allowing all external systems to call the same web service.

Download the sample code below that shows how easy it is to modify an existing BizTalk Web Service to accept Xml Documents rather than a typed message. The key to routing the message submitted by the Web Service is to make sure the Receive Port uses the Xml Disassembler pipeline. This will promote the message type property that can be used for routing.

As with any direct binding solution, extra care must be used to make sure you do not get into an endless loop. In the sample below, I use Message Type and Receive Port Name Exists to make sure only messages published by a receive port are routed to the Orchestration.

Download: Xml Documents to BizTalk via SOAP Sample

This sample was built using BizTalk 2006 using Visual Studios 2005. See the ReadMe.txt for set up information.

Mapping: Another Inline XSLT Scenario

by community-syndication | Mar 13, 2006 | BizTalk Community Blogs via Syndication

New Page 1

In keeping with
my mapping topics I have another inline XSLT scenario. In my last XSLT scenario
entry
I discussed grouping by countries and then cities.

In this entry I
want to look at a somewhat related scenario. In this scenario I receive an
instance document that contains account numbers and associated amounts. We need
the account numbers grouped and the amounts summed by account number.

The instance
document looks like:

<ns0:Root
xmlns:ns0="http://Blogtopic.SchemaSrc">

</Source>

</ns0:Root>

The source
schema looks like:

And the
destination schema looks like:

As mentioned in
my last XSLT blog entry there isn’t a way to group records in the BizTalk Mapper
so we will use a script functoid with custom XSLT.

The first thing
we will need to do is to drag a Script functoid onto the Grid. Then drag a line
to the element that we will create the output for. Again, with the inline XLST
functionality we are required to produce the output for all of the elements
underneath the records our functoid is attached to. So in this example we are
responsible for creating all of the content under the Destination record.

The map will
look like this:

The following
XSLT can be cut and paste (without the line numbers) into the Inline Script
Buffer section of the script functoid after the Script Type drop down has been
set to Inline XSLT

To create the
output in the format that we want we will start with the following XLST.

<xsl:variable
name="unique-accountNbr"

select="//Source/Row[not(@AccountNbr=preceding-sibling::Row/@AccountNbr)]/@AccountNbr"
/>

This creates a
variable named unique-accountNbr and populates it with the list of unique
account numbers. Now we need to output the nodes and loop through the account
numbers. The following code will accomplish that.

1.
<Destination>

2. <xsl:for-each
select="$unique-accountNbr">

3.
<Account>

4. <xsl:variable
name="accountSum" select="sum(//Source/Row[@AccountNbr=current()]/@Amount)" />

5. <xsl:attribute
name="AccountNbr">

6. <xsl:value-of
select="current()" />

7. </xsl:attribute>

8. <xsl:attribute
name="AccountSum">

9. <xsl:value-of
select="$accountSum" />

10. </xsl:attribute>

11.
</Account>

12. </xsl:for-each>

13.
</Destination>

Lets walk
through this code.

Lines 1 and 13
create the Destination node output and close the node.

Line 2 creates
the loop that will loop through all of the unique account numbers.

Lines 3 and 11
create the Account node output and close the node

Line 4 creates
another variable for the sum of the amounts by account number

Lines 5 and 7
create the AccountNbr attribute and close it

Line 6 takes the
current account number in the loop and writes the value to the attribute

Lines 8, 9 and
10 repeat what was just done for the AccountNbr attribute but this time for the
AccountSum attribute. Also notice that the value of the accountsum variable is
used.

This code will
loop through as many unique account numbers as are contained in the input
document. Then for each unique account number the code will sum up all of the
Amount attributes associated with the unique account numbers.

The output looks
like this:

<ns0:Root
xmlns:ns0="http://BlogTopic.SchemaDst">

</Destination>

</ns0:Root>

BizTalk 2004 – Visual Studio 2003 Preinstall Failure

by community-syndication | Mar 13, 2006 | BizTalk Community Blogs via Syndication

I have had to park my work on BizTalk 2006 while I assist another team at my current client on some BizTalk 2004 work. I was helping to install and configure some BizTalk groups and remembered just how much of a pain this can be and how much this has evolved in BizTalk 2006.

Anyway, I had an issue during the pre-installation of Visual Studio 2003 on a Windows 2003 SP1 server that took me some time to fathom out. Checking the error log highlighted the following errors in particular:

[03/13/06,10:57:24] Microsoft FrontPage 2000 Web Extensions Client: ***ERRORLOG EVENT*** : Error code 1603 for this component means “Fatal error during installation.

[03/13/06,10:57:24] Microsoft FrontPage 2000 Web Extensions Client: ***ERRORLOG EVENT*** : Setup Failed on component Microsoft FrontPage 2000 Web Extensions Client

[03/13/06,10:57:27] Setup Runtime Files: ***ERRORLOG EVENT*** : Error code 1603 for this component means “Fatal error during installation.

[03/13/06,10:57:27] Setup Runtime Files: ***ERRORLOG EVENT*** : Setup Failed on component Setup Runtime Files

[03/13/06,10:57:30] Microsoft Visual J# .NET Redistributable Package 1.1: ***ERRORLOG EVENT*** : Error code 1603 for this component means “Fatal error during installation.

[03/13/06,10:57:30] Microsoft Visual J# .NET Redistributable Package 1.1: ***ERRORLOG EVENT*** : Setup Failed on component Microsoft Visual J# .NET Redistributable Package 1.1

[03/13/06,10:57:32] setup.exe: ***ERRORLOG EVENT*** : Unable to open g_szRegkeyMSIntegration in CSetupManager::RemoveSetupFiles()

[03/13/06,10:57:32] setup.exe: ***ERRORLOG EVENT*** : CSetupManager::RemoveSetupFiles() failed in CSetupManager::RunInstall()

[03/13/06,10:57:32] VS70BaseUI: ***ERRORLOG EVENT*** : DepCheck indicates Microsoft FrontPage 2000 Web Extensions Client is not installed.

[03/13/06,10:57:32] VS70BaseUI: ***ERRORLOG EVENT*** : DepCheck indicates Setup Runtime Files is not installed.

[03/13/06,10:57:32] VS70BaseUI: ***ERRORLOG EVENT*** : DepCheck indicates Microsoft Visual J# .NET Redistributable Package 1.1 is not installed.

After some considerable amount of time researching what could be the root cause, I stumbled across the following KB article that led me to the answer http://support.microsoft.com/default.aspx?scid=kb;en-us;327763

In my scenario the servers that I received had an incorrect mapping for the “My Documents” folder, which pointed to a network folder that did not exist. I changed this to point to a temporary folder located on the local machine and the installation continued successfully.

I hope this post may help save someone else time.