Tom Babiec wrote a great blog a few months back on inserting multiple parent child tables in a single stored procedure. We use this technique a lot in our data integration work, and it’s proven to be very robust in many contexts. The SQL procedure outlined in that blog is useful not just for BizTalk, but generally speaking for ADO.NET and other applications trying to load multiple rows into SQL Server for multiple parent child tables. However, when dealing with larger datasets (and as the table grows), we’ve noticed some degradation in performance. In some cases, we were seeing variances of 30 seconds to 10+ minutes for loading the same exact data set on the same database. We tried a few different options, including forcing a recompile of the stored procedure between each load
, but this did not solve…
Databases are very frequently at the heart of an enterprise integration. EAI tasks frequently involve polling databases, calling stored procedures in databases, as well as ETL and basic CRUD work on databases. The database backing a particular application is often a natural source to integrate with – and if no on premise database exists for a particular source, creating one for local OLTP purposes can help increase insight and decrease chattiness between on premises and off premises applications.
It’s no wonder then that both BizTalk and Mulesoft ESB offer database connectivity out of the box. In this post, I’ll compare Mule and BizTalk’s SQL capabilities, primarily focusing on Oracle and SQL Server. Why these two? They’re the most popular enterprise grade database engines. MySQL is also very popular, but lacking in some enterprise features that this blog will be examining. PostgresSQL…
This post covers how to load records into several tables that have parent-child relationships, via a single stored procedure call and the use of SQL user-defined table types. You may have seen how to use table types for BizTalk SQL loads before (such as in this post), but the approach detailed below shows how to load multiple parent and child records within the same stored procedure, while also executing no more than one insert statement for each table being loaded. (Note that user-defined table types is only available in SQL Server 2008 or later).
Having BizTalk make just one SQL stored procedure call for all records has multiple benefits:
Reduces the number of BizTalk messages going through the BizTalkMsgBoxDb database, as well as the number of roundtrips between BizTalk and SQL Servers, reducing overhead and improving speed of loads
All record loads can…
If you have ever used the Table Looping functoid in a BizTalk map, then you’re aware of its ability to take multiple inputs (whether they’re links from a source schema or other functoid outputs), and configure them as a table looping grid, so that these values can be used to create multiple nodes in the destination schema. Essentially, this functoid can be used to unpivot repeating or flattened data into multiple nodes.
For example, in the mapping below, I’m taking a flat file that has repeating fields to represent ten Line Items, all on a single record. Using the Table Looping functoid, I can map these repeating fields to create multiple LineItem records in my destination.
The ESB Toolkit 2.1 Exception Handling Framework provides the ability to easily report and and alert on both routed failed messages (where Failed Message Routing is enabled on a Receive or Send Port), as well as ESBFaults created from exceptions within an Orchestration.
However, if you try routing failed messages on a Send Port that is bound to a Logical Port that has Delivery Notification enabled, you’ll get a Send Port failure with the following exception:
If you’ve worked with Receive Shape filters in BizTalk Server 2006 R2, you may have found that updating existing filters is sometimes problematic, in that the change isn’t always applied after deploying it.
I set out to find out when this issue occurs, and what the resolve is. I added a condition to an existing Receive Shape filter, and deployed to see if the change was applied (new filter conditions are highlighted in red):
After deploying, I ran a query in the Administration Console Group Hub to check the subscription…
I ran the query, and in the results I clicked on the orchestration (for which I modified the Receive Shape Filter) to see the subscription details (new conditions highlighted in red):
In this case, after adding new conditions to the filter, there were no issues in the changes being applied after deploying. So…
When trying to send a message via the SMTP adapter in BizTalk we have the option of attaching messages as part of Multi-Part Messages. If the message being attached is too large the message will fail to send. In our application, we send email notifications of failed messages in BizTalk with the original message sent as an attachment.
An solution we devised was to compress the original message and attach it to the email. This solution, however, presented its own considerations.
We didn’t want to create a temporary directory on a server to create the compressed file in. We wanted to do all of the compression in memory.
Since we were compressing in memory we had to come up with a solution to attach the file to the email message.
The compression in memory is discussed in my posting on File Compression In Memory. …