RESTfully GETting JSON Formatted Data with BizTalk 2013

With the new WCF-WebHttp Adapter that shipped in BizTalk Server 2013 we now have the ability to specify which HTTP verbs to use – this opens up the possibility of invoking RESTful style applications.

In this post, I’ll investigate how we can RESTfully invoke an endpoint that exposes data via JSON using the GET verb – we’ll be ‘RESTfully GETting JSON Formatted Data with BizTalk 2013′; I’ll be retrieving stock tickers from Yahoo! Finance in JSON format.

Send/Receive Adapter

The RESTful endpoint is invoked via a solicit/response Send Port configured to use the WCF-WebHttp Adapter, providing the Yahoo! Finance URL and a single HTTP method of ‘GET':

WCF-WebHttp Adapter Configuration

 

JSON Decode Receive Pipeline Component

The JSON response is passed to a custom Receive Pipeline via the adapter which contains a newly developed ‘JsonDecode’ component. This component invokes the fantastic Json.Net package (see http://james.newtonking.com/pages/json-net.aspx for more information) to do the required heavy lifting and convert the JSON into an Xml representation. The Execute() method of the Pipeline Component looks something like this:

JSON Decode Pipeline Component Source Code

Once we have an Xml representation of the JSON response, we add a Root Node (and associated namespace) so we can leverage our usual magic BizTalk sauce. The root node name and namespace are exposed as properties via the Pipeline Component (think WCF-SQL Adapter):

JSON Decode Pipeline Component Configuration

Simply create a schema that represents your new message and voilà, you’re receiving JSON data and processing it as an Xml message within BizTalk; you’ll also have access to all of the usual property promotion/routing capabilities etc.

JSON in, Xml Out

Now that we have configured our solution, what do the input and output messages look like?

Something like this for the JSON response to our GET request:

JSON Response Data

and our corresponding Xml representation:

JSON Data as Xml Message to be Consumed by BizTalk

Feel free to download a copy of the solution if you’re interested –> Modhul.PipelineComponents.JsonDecode.zip. Please note that the sample code is for demonstration purpose, and it is NOT production quality code.

Stay tuned for Part 2, where I will look at POSTing JSON data from BizTalk.

 

Beware of windows.old when upgrading to Windows 8

I have just taken the plunge and upgraded from Windows 7 to Windows 8 (partly because of the £25 upgrade offer). After the upgrade I noticed a severe lack of space on my otherwise roomy C: drive.

A quick check in Windows Explorer reveals a windows.old directory which contains all of my old Windows 7 ‘Windows’ directory, Program Files, Users and PerfLogs directories – and its just short of 20Gb. I’ll be shifting it to an external backup drive shortly…

windows.old

Including Custom Databases in the Backup BizTalk Server Job

I’m sure its common knowledge that other databases can be backed-up using the Backup BizTalk Server Job, meaning that the transaction consistency that is that is written the the BizTalk databases can be included in your own custom databases.

I thought it was pretty easy to include custom databases, simply by adding an entry into the adm_OtherBackupDatabases table in the BizTalkMgmtDb database. In the screenshot below, I’ve added the BizTalk RosettaNet Accelerator (BTARN) databases:

I thought it was that simple, until I tried it for the first time in real life and was greeted with the following error from SQL Agent (notice the error in bold):

Executed as user: NT AUTHORITYSYSTEM. Processed 17520 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 17522 pages in 10.454 seconds (13.093 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 2264 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 3 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 2267 pages in 2.284 seconds (7.751 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 3280 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb_log’ on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 3282 pages in 2.156 seconds (11.890 MB/sec). [SQLSTATE 01000] (Message 3014)  Processed 256 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 1 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) Could not find stored procedure ‘BTARNARCHIVE.dbo.sp_BackupBizTalkFull’. [SQLSTATE 42000] (Error 2812) BACKUP DATABASE successfully processed 257 pages in 0.326 seconds (6.158 MB/sec). [SQLSTATE 01000] (Error 3014).  The step failed.

‘Could not find stored procedure ‘BTARNARCHIVE.dbo.sp_BackupBizTalkFull’. Oh really?

It turns out that it isn’t as simple as adding a custom database to the adm_OtherBackupDatabases table – a number of stored procedures and tables also need to be added to your custom database to get the backup job to successfully work, however these can be easily added by executing the two SQL scripts Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql against your custom databases (the scripts can be found in the ‘[BizTalk Installation Directory]Schema’ directory). You may want to force a full backup to ensure you have full backups across all of your databases:

BEGIN TRAN
UPDATE [BizTalkMgmtDb].[dbo].[adm_ForceFullBackup] SET ForceFull = 1
COMMIT TRAN

Re-run your backup job and you will see your custom databases included in the backup.

The requirement to include additional stored procedures and tables in custom databases is well worth remembering, especially if you are deploying (or re-deploying) custom databases that don’t include these artifacts by default.

The procedure is detailed in more depth on MSDN.

BTARN ‘Service Content’ Error in the RNDisAssembler Component

We came across the following error late last night which was a bit of a show-stopper. We were trying to load a custom PIP (specifically a PIDX OrderChange), but kept hitting this issue time and time again:

Event Type: Error
Event Source: BizTalk Accelerator for RosettaNet
Event Category: None
Event ID: 4096
Description:
Source module:
RNDisAssembler
Correlation information:
Description:
Receive pipeline rejected incoming message
due to the following RNIF exception:
UNP.SCON.VALERR : A failure occurred while validating the service content.
Details:
Event Type: ErrorEvent Source: BizTalk Accelerator for RosettaNetEvent Category: NoneEvent ID: 4096Date: 18/08/2010Time: 10:33:47User: N/AComputer: [COMPUTER NAME]Description:Source module:RNDisAssembler
Correlation information:

Description:

Receive pipeline rejected incoming message due to the following RNIF exception: UNP.SCON.VALERR : A failure occurred while validating the service content.
Details:

At first I didn’t quite understand the cause of the error – the PIDX OrderChange message contained within the Service Content was valid (as far as I was aware), all of the other messages within the payload looked correct and it was 3am….

It turns out that the RNDisassembler does in-fact attempt to validate the message contained within the Service Content against a deployed schema just like the standard XmlDisassembler. The message that our trading partner was sending did not validate and hence the RosettaNet Accelerator threw this error message; once we had corrected the schema and redeployed, the error went away.

This is certainly one to be aware of if you are developing custom PIP’s to use with the RosettaNet Accelerator: ensure that the message in the Service Content validates against your custom PIP schema!

SCOM can cause Unchecked Growth in SSODB

Ok, that’s a bit of an attention grabbing headline, so let me clarify that statement: SCOM can cause unchecked growth in SSODB if you’re not regularly backing up the SSODB transaction log.

We encountered this one today – a client’s SSODB ran out of space overnight, causing the BizTalk environment to shut-down. On further investigation, it would appear that every time SCOM checks the health of Enterprise Single Sign-On, an entry is recorded in the SSOX_AuditXpLookup table:

ESSO appears to be clever enough to manage the size of this table, truncating it every 30 minutes, however this doesn’t help if you’re not managing the size of the database transaction log through backups. To quote the SQL Server documentation:

If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.

All the more reason to enable and run the supplied Backup BizTalk Server job to help maintain the health of your BizTalk environment.

If you’re looking for more information on the Backup BizTalk Server Job, take a look at my series of posts on the topic: http://www.modhul.com/2009/06/29/configuring-biztalk-for-disaster-recovery-part-1/

Hat-tip to this MSDN Forums posting that helped us identify the issue.

BizTalk Server 2010 Beta Released!

Update 2 (24th May 2010): The beta is back, download it now at the link below.

Update: Well, it was there about four hours ago, but the link no longer works…. I’m pleased to say that it isn’t just me who got all excited. I’ll update this post if the link becomes active again. In the meantime, I did manage to download a copy, so will post my experiences here.

Looks like the beta of BizTalk Server 2010 was released today – three words: Windows 7 Support!

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0f852e77-f792-4784-b2d4-95113d40db64

Hat-tip: Nick M.

Further MS-DTC Issues – Check the Startup Order of Clustered Services

We’ve just encountered an obscure MSDTC/SQL Server issue that I thought would be beneficial to the wide-community.

Following a failover of our (Windows Server 2008) cluster, we started to encounter unexpected errors when BizTalk attempted to perform any tasks that required a distributed transaction, even though everything appeared to be running correctly: MS-DTC was running and we could DTCPing the BizTalk Server from the SQL Server (and vice-a-versa), so no issues with DTC; SQL Server was also running as we could connect to the instance via Management Studio and BizTalk could read the Management Database etc., yet DTC operations still failed with the following error:

Enlist operation failed: 0x8004d01c(XACT_E_CONNECTION_DOWN). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.
A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 8510)

It turns out that when clustered services are brought online, they must be started in a specific order, with the DTC service being started before SQL Server. If they are not brought online in this order, SQL Server fails to register itself and DTC transactions cannot be initiated, even though both the DTC and SQL service’s are running and everything looks correct.

Thanks to this forum post for providing us with the much needed pointer: http://www.ureader.com/msg/1142121.aspx.