We heavily use the BizTalk Deployment Framework to aid in the deployment of simple to complex BizTalk solutions. One of the greatest benefits is the ability to run custom deployment tasks and scripts. In previous blogs we have shown how to run a custom post deployment scripts in BTDF, today, we will show how custom scripts can be used to conditionally import a binding file during BTDF deployment.
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…
The Tallan Integration Practice heavily utilizes Azure Infrastructure as a Service (IaaS) virtual machines for quickly setting up isolated environments for prototypes and BizTalk development environments. However, with so many virtual machines it is often difficult to remember to shut them off when not needed. With Azure‘s Automation features, we can setup a scheduled job to shut down non mission critical VM’s, saving on hosting costs.
There are a few good resources out there for setting up a clustered Master Secret Server out there:
Clustering the Master Secret Server (MSDN)
Installation of SSO on a SQL Failover Cluster
However, I faced some issues recently setting all of this up, getting the following errors (in the event log and the configuration log):
Creation of Adapter FILE Configuration Store entries failed. (BizTalk config log)
Could not import a DTC transaction. Please check that MSDTC is configured correctly for remote operation. See the event log (on computer EntSSOClusterResource) (BizTalk config log)
d:\bt\127854\private\source\setup\btscfg\btscfg.cpp(2213): FAILED hr = c0002a25 (BizTalk Config log)
Failed to initialize the needed name objects. Error Specifics: hr = 0x80004005, com\complus\dtc\dtc\msdtcprx\src\dtcinit.cpp:575, CmdLine: “C:\Program Files\Common Files\Enterprise Single Sign-On\ENTSSO.exe”, Pid: 172 (Event log)
Could not import a DTC transaction. Please check that MSDTC is configured correctly for remote operation. See documentation for details. Error Code: 0x80070057, The parameter is…
I was working on a message flow that involved routing the response from a Request-Response send port directly to another send port. The ultimate send port had failed message routing turned on so that messages would get properly routed by the ESB ToolKit to the EsbExceptionDb for further analysis or processing. In this particular case, the send port was an SFTP port with a misconfigured username/password combination, which would result in failed messages. However, when the message failed I’d get the following exception in the EventLog:
Value cannot be null.
Parameter name: guid
Method: Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext, Microsoft.BizTalk.Message.Interop.IBaseMessage)
Error Source: Microsoft.Practices.ESB.ExceptionHandling
Error TargetSite: System.String GetApplication(ServiceType, System.String, System.String, System.String, System.String)
Error StackTrace: at Microsoft.Practices.ESB.ExceptionHandling.Utility.GetApplication(ServiceType serviceType, String mgmtDBServer, String mgmtDBName, String guid, String serviceName)
at Microsoft.Practices.ESB.ExceptionHandling.PipelineComponents.ProcessFault.WriteHeaderFailedMessageRouting(XmlTextWriter writer, IBaseMessage pInMsg, Object& portName)
at Microsoft.Practices.ESB.ExceptionHandling.PipelineComponents.ProcessFault.WriteHeader(XmlTextWriter writer, XmlTextReader reader, FaultSource faultSource, IBaseMessage pInMsg, Object& portName)
at Microsoft.Practices.ESB.ExceptionHandling.PipelineComponents.ProcessFault.Execute(IPipelineContext pContext, IBaseMessage…
An integration scenario requires a unique incrementing numeric identifier to be sent with each message (or each record in a message). These identifiers cannot be reused (or cannot be reused over certain ranges, or cannot be reused over certain periods of time). A GUID is not suitable because it will not be sequential (not to mention that many legacy systems and data formats may have trouble handling a 128 bit number!).
Integration platforms will have a hard time meeting this on their own – GUIDs work well because they guarantee uniqueness on the fly without needing to worry about history. Messaging platforms typically deal in terms of short executions, and BizTalk is no exception. While persistence of a message might be handled (such as BizTalk does with the MessageBox), persistence of the entire execution process is usually not guaranteed. Deployments,…
Aggregation is a common pattern used in Enterprise Integration. System (or systems) A sends many messages that System B expects in a single message, or in several messages grouped on a particular attribute (or set of attributes).
The most common way to approach this in BizTalk is using a Sequential Convoy orchestration to aggregate the message – Microsoft provides a sample of how to do this in the SDK. This is a powerful pattern, but has a few downsides:
Sequential Convoys can become difficult to manage if they’re expected to run for a long time
Complex subgrouping can multiply the headaches here – for example, if you have to aggregate messages for hundreds of destinations concurrently
The destination message may become very large, to the point where BizTalk cannot optimally process it anymore – particularly if it is a large flat file message.
Modifying the aggregate…
Oracle packages can leverage VARRAY types to enable a caller to send a collection of values (an array) to a stored procedure in a single call. This VARRAY is very much like a single column Table Type in SQL Server. Generating the schema for this procedure required creating a class/assembly for the ODP.NET driver to use, which in turn is used by BizTalk. That process is fairly straightforward and documented well here. The one point missing from that blog is that the VARRAY object has to have an INDEXED BY INT in order for BizTalk to be able to use it; this is hinted at by the MSDN article on Limitations in the Oracle Adapter:
The Oracle Database adapter does not support PL/SQL tables that are not indexed by a numeric field.
It seems this limitation applies to VARRAY types (which makes sense…
I wrote previously (here) about using SQL Server Profile to capture and debug SQL calls from BizTalk. This method works well when there are no errors actually calling the procedure (but you want to tune the procedure using ‘real’ data from BizTalk). However, it’s not as much help when BizTalk can’t call the procedure at all – because of an invalid conversion, or a malformed table type, or some other errors in the procedure that prevent the procedure from actually being run. Either no event will get logged in the Profiler, or you will see only the stored procedure name but no parameters (because BizTalk realizes the procedure won’t be able to correctly execute).
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…