EAI Bridge Message Sender and Queue Message Receive tool

by community-syndication | Sep 7, 2012 | BizTalk Community Blogs via Syndication

In the last couple of months I’ve been busy participating in several national and international events talking about Azure Service Bus EAI/EDI services, or as we now know: BizTalk as PaaS (BizTalk on the cloud). You can find it more information here. There are several tools available in Windows Azure Service Bus EAI & EDI […]
Blog Post by: Sandro Pereira

New version of BizTalk Mapper Extensions UtilityPack available with new functoids

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

I just release a new version of “BizTalk Mapper Extensions UtilityPack” project (available on CodePlex and Code Gallery) with a new set of functoids for BizTalk Server 2010 (I will soon publish for older versions). Project Description BizTalk Mapper Extensions UtilityPack is a set of libraries with several useful functoids to include and use it […]
Blog Post by: Sandro Pereira

BizTalk360 continues to evolve: The fifth release in Beta

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

Monitoring is essential to keep your BizTalk environments healthy and running smoothly. With monitoring you have a couple of options. Either using System Center Operation Manager, a combination of SCOM and third party tooling or solely use third party tooling.

1. Monitoring the BizTalk environment with SCOM can be done with using the management pack for BizTalk Server or AVIcode .NET Application Management Pack.

2. An alternative monitoring solution is BizTalk360, which you can combine with using SCOM. This means you can have best of both worlds.

3. In case your enterprise does not have SCOM or is planning to deploy SCOM, BizTalk360 itself can be an excellent choice for monitoring BizTalk environments.

To efficiently monitor a BizTalk environment people with different roles are involved like:

%u00b7 System Administrator,

%u00b7 Database Administrator and   

%u00b7 BizTalk Administrator.

Each has different needs for information to be able to resolve or prevent issues. BizTalk360 can aid in full filling the needs for a BizTalk administrator as it will give him/her the fine grained detail of the environment and in-depth information. Besides that it has many capabilities onboard that SCOM lacks. For instance fine grained governance, throttling analyzer, Message Box Viewer Reports and Graphical Message View. The productivity of a BizTalk administrator can be dramatically enhanced using BizTalk360. BizTalk360 is provided by Kovia ltd and about to release the fifth release. The new capabilities are:

Monitoring Dashboard  

Backup – Disaster Recovery configuration visualizer   

Process monitoring for receive locations and send ports (aka silence/inactivity monitoring)   

HP Operation Manager Integration   

Support for Message Box Viewer (MBV) 12   

Run MBV anytime directly from UI  

New Event Viewer notification channel Disable individual alerts

Performance Improvements

Better applications loading time   

Improved Environment/Platform setting dashboard loading time  

Improved monitoring service performance

The new capabilities and improvements represent another cycle of evolution this product is going under. Which means that it continues to grow to a fully-fledged product with an outstanding set of capabilities for monitoring your BizTalk environment. BizTalk360 supports BizTalk versions 2006 and up. You can download the BizTalk360 version 5 Beta now and try it yourself on your local or test BizTalk environment.

Formatting code to HTML

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

I like to share a website I frequently use to format any code (C#, XML or T-SQL) to HTML for using on my blog. Other possibilities are to format VB, HTML or msh, but I haven’t used these myself yet.
You can also add things like line numbers, or use alternating backgrounds. And to modify the colors of the output, you’ll just need to change the css style sheet.

Also because everytime I need it, I’ll have to search the website again on google. So this way I’ll save some time for me too :).

Here’s the link: http://www.manoli.net/csharpformat/

Create header names for BizTalk flat file assembler

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

It isn’t that hard to create a flat file from XML file, but I had a bit of trouble to find out how to create a header line with the column names, so I thought I would write a short blog post to describe this.

The following steps are needed:

1. Take a copy of your flat file schema. Remember to change the file name and type name of the schema
2. Open the new schema and change the name of your root element or your target namespace. Otherwise you will get an error that BizTalk can’t find your schema as it might not have been added to the GAC. The reason for this error is that BizTalk needs a unique combination of target namespace and root element.
3. For each element in your schema you have to set a default value (the name of the column) and it needed change the datatype of the element to string.
4. In the send pipeline flat file assembler component you have to point this new schema that you have created.
5. This should be it. Of cause you need to deploy, restart and so on, but you know this 😉

Delete BizTalk backup files

by community-syndication | Sep 5, 2012 | BizTalk Community Blogs via Syndication

The SQL Server agent job “Backup BizTalk Server” will not delete the generated backup files automatically. The job does clear the backup history table in the database, but it will never delete the backup files from the disk. Which will of course result in the disk to fill up eventually and the backup job will fail from then on.

Here’s a simple stored procedure to call from the “Backup BizTalk Server” job in the “Clear Backup History” step. Just follow these steps:

1. Open SQL Server management Studio
2. Open a new query window and connect to the BizTalkMgmtDb database
3. Execute this script to add a new stored procedure called sp_DeleteBackupHistoryAndFiles
   

   
   USE [BizTalkMgmtDb]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null, @UseLocalTime bit = 0
   
   AS
    BEGIN
   
    set nocount on
   
     IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
     RETURN
   
        /*
           Only delete full sets
           If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set
   
           Delete history only if history of full Backup exists at a later point of time
           why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
           If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
       
       */
           
     declare @PurgeDateTime datetime
   
     if (@UseLocalTime = 0)
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
     else
       set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())
   
     DECLARE DeleteBackupFiles CURSOR
   
     FOR SELECT 'del "' + [BackupFileLocation] +  case right(BackupFileLocation,1) when '\' then '' else '\' end + [BackupFileName] + '"' FROM [adm_BackupHistory] [h1]
       WHERE     [BackupDateTime] < @PurgeDateTime
       AND    [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
       AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')
   
     DECLARE @cmd varchar(400)
   
     OPEN DeleteBackupFiles
   
      FETCH NEXT FROM DeleteBackupFiles INTO @cmd
   
     WHILE (@@fetch_status <> -1)
     BEGIN
               IF (@@fetch_status <> -2)
               BEGIN
   
                           EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
   
                           delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
   
                           print @cmd
               END
   
               FETCH NEXT FROM DeleteBackupFiles INTO @cmd
     END
   
     CLOSE DeleteBackupFiles
   
     DEALLOCATE DeleteBackupFiles
   
     END
   

4. 5.Modify the “Clear Backup History” step of the Backup BizTalk Server job to call sp_DeleteBackupHistoryAndFiles, instead of calling the stored procedure sp_DeleteBackupHistory
5. Make sure xp_cmdshell for the SQL Server instance is enabled. this will be disabled by default. To enable this, execute following SQL script:
   

   
   EXEC master.dbo.sp_configure 'show advanced options', 1
   
   RECONFIGURE
   
   EXEC master.dbo.sp_configure 'xp_cmdshell', 1
   
   RECONFIGURE
   
   

This stored procedure is made for BizTalk Server 2010.
Because with the release of BizTalk 2010 there have been changes to the sp_DeleteBackupHistory stored procedures. They added a parameter to the stored procedure to use local time and changed the query to prevent the deletion of the history from the last full backup set forward.

For older versions of BizTalk you should use this script instead:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndFiles] @DaysToKeep smallint = null
AS
BEGIN
set nocount on
  IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
  RETURN
/*
  Only delete full sets
  If a set spans a day such that some items fall into the deleted group and the other doesn't, do not delete the set
*/ 

DECLARE DeleteBackupFiles CURSOR
FOR SELECT 'del "' + [BackupFileLocation] + '\' + [BackupFileName] + '"' FROM [adm_BackupHistory]
WHERE  datediff( dd, [BackupDateTime], getdate() ) >= @DaysToKeep
AND [BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [BackupSetId] AND datediff( dd, [h2].[BackupDateTime], getdate() ) < @DaysToKeep )
DECLARE @cmd varchar(400)
OPEN DeleteBackupFiles
FETCH NEXT FROM DeleteBackupFiles INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN 

            IF (@@fetch_status <> -2)
            BEGIN
                        EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
                        delete from [adm_BackupHistory] WHERE CURRENT OF DeleteBackupFiles
                        print @cmd
            END 

            FETCH NEXT FROM DeleteBackupFiles INTO @cmd
END 

CLOSE DeleteBackupFiles
DEALLOCATE DeleteBackupFiles
  END
GO

Source: http://www.biztalkbill.com/Home/tabid/40/EntryId/81/Update-to-Stored-Procedure-to-delete-Backup-BizTalk-Server-SQL-Agent-backup-files.aspx

Developing BizTalk 2010 R2 with Visual Studio 2011??

by community-syndication | Sep 4, 2012 | BizTalk Community Blogs via Syndication

Since a couple of days Microsoft enabled Windows Azure users to create Virtual Machines, which are based on BizTalk 2010 R2 CTP. This is great, because it offers those users to have a preview at that release, combined with some of the new related products, like SQL Server 2012 and .NET 4.5.

However, given the screenshot below, which was taken from the BizTalk 2010 R2 Installation Wizard, it looks like Microsoft expected to release the next version of Visual Studio sooner 🙂