How to test a pipeline

You can find in this link

http://geekswithblogs.net/michaelstephenson/archive/2008/03/30/120852.aspx

Inside you can find a sample to use XPathMutator to quick ly extract Xml using XPath queries

How to build a message in helper

I've found wonderful post about building a message inside orchestration (via helper)

http://blogs.msdn.com/paolos/archive/2009/09/10/4-different-ways-to-process-an-xlangmessage-within-an-helper-component-invoked-by-an-orchestration.aspx

My hobbie - diving and shooting

Connecting to Oracle via SQL Adapter

In a specific scenario, we needed two receive location bounded to Oracle db.
One was simple, we needed a table rows to start a process. This was eazy using BizTalk Oracle Adapter. Installing Oracle client, defining TNS, and connection establish.

The other data we needed was a bit more complex. We needed the employee courses including subject learned in the course.

The Oracle adapter can not receive XML queries as result, so we preffered to define a linked server from SQL to Oracle, define the query SQL with the linked server like that:

Select *
from
OpenQuery(Ora,"Select empid,courseid,x1....xn from courses") course,
OpenQuery(Ora,"Select empid,courseid,y1...ym from subjects") subject
where
course.empid = subject.empid and
course.courseid = subject.courseid
for xml auto

the query worked fine in SQL Managment studio. But the receice location query failed in the adapter since the BizTalk tried to add the Oracle DB to the distributed transaction belongs to the SQL Server, over the linked server.

When searching this issue, I came accross a blog, Joe unfiltered: BizTalk, SQL linked servers and DTC trying to do the same.

Following the post solution, we created new linked server, adding the string "DistribTX=0" to Provider settings (and selecting Oracle OLE DB Provider - instead of MSDAORA), which solve the issue.

Shimshon Fishler
שמשון פישלר

Sending email attachments from BizTalk orchestration

There are some ways to add attachments to outgoing email from BizTalk orchestration.

One method is the use dynamic send port and to set output message content with the attachments paths.

The syntax for the "SMTP.Attachements" property is File1File2...

Sample:

Msg(SMTP.Attachements)="C:\Temp\smpt1.jpgC:\Temp\smpt2.jpg";

Shimshon

My project award

Recently, my project won an honrable award from Microsoft.Israel in the category "Best Enterprose Solution based on MS Application platform - Dev"

This project is in the Israeli Tax Authorities, based on BizTalk 2006 R2 server.

Some links (hebrew posts)

http://www.pc.co.il/?tag=שמשון-פישלר

The project story in Microsoft customers case studies site 

http://www.microsoft.com/israel/casestudies/property.mspx

The project award:

http://www.facebook.com/photo.php?pid=1559914&l=12bd173e1d&id=689225628

BizTalk 2006 / 2009 SQL Adapter

When working with SQL Server data in BizTalk Server, there are several differences between using the BizTalk SQL Adapter vs. using .Net assemblies for executing SQL stored procedures and manipulating data using CRUD (Create, Read, Update, Delete –see more on http://en.wikipedia.org/wiki/Create,_read,_update_and_delete
One of there is the behavior of persistency points when orchestrations are involved.
Using the SQL Adapter, by adding one or more receive and send ports to the scenario, the orchestration will stop and a persistency point will be created by BizTalk, and followed by sending (or receiving) the message to (or from) the port.
Contrary, when using .Net assemblies, the SQL command performed is part of the transaction, and not causing any persistency points.
Since working with SQL adapter cause persistency points and therefore increase overall required time needed to complete the operation, comparing to execute a custom stored procedure via Expression share.
But when working with long running SQL Stored procedures, invoking them via Expression shape will keep the orchestration in active state wasting BizTalk resources. Executing the same SP via SQL Adapter will cause the orchestration to be dehydrated when sending the message to port, and resume when SP will end.
Although using SQL Adapter is a bit complicated (see: http://msdn.microsoft.com/en-us/library/cc507804.aspx) there are benefits to use it.
Note that the current BizTalk 2006 SQL Adapter is obsolete in BizTalk 2009 (meaning it will be discontinue to the next version) and BizTalk 2009 introduce new WCF based SQL Adapter.
Shimshon.
(Posted also on: http://www.g-s.co.il/193.html)