[BizTalk] Automatically create folders configured for file adapters in BizTalk Applications

After installing and configuring a BizTalk application, we often MANUALLY create the folders configured for receive locations and send ports. This process is quite cumbersome if you have an a large number of folders to create.

While deploying a huge BizTalk application for one of my clients, I developed a small c# console application which accesses the BizTalk Management database to retrieve a list of configured folders and then creates these folders in the Windows local file system.

Here is the SQL script, I used to retrieve the list of FILE receive locations and send ports:

USE [BizTalkMgmtDb]

GO

-- Author: Shah MOHAMOD

-- Description: Retrieves the FILE receive locations and send ports information from the BizTalk management database

SELECT *

FROM

(

-- Get FILE receive locations

SELECT [dbo].[bts_application].[nvcName] AS ApplicationName, [Name], [InboundTransportURL] AS URI, 'Receive' AS LocationType

FROM [dbo].[adm_ReceiveLocation]

INNER JOIN [dbo].[bts_receiveport] ON [dbo].[adm_ReceiveLocation].[ReceivePortId] = [dbo].[bts_receiveport].[nID]

INNER JOIN [dbo].[bts_application] ON [dbo].[bts_receiveport].[nApplicationID] = [dbo].[bts_application].[nID]

WHERE [AdapterId] IN

(

SELECT [Id]

FROM [dbo].[adm_Adapter]

WHERE [Name] = 'FILE'

)

UNION ALL

-- Get FILE send ports

SELECT [dbo].[bts_application].[nvcName] AS ApplicationName, [dbo].[bts_sendport].[nvcName] AS [Name], [nvcAddress] AS URI, 'Send' AS LocationType

FROM [dbo].[bts_sendport_transport]

INNER JOIN [dbo].[bts_sendport] ON [dbo].[bts_sendport_transport].[nSendPortID] = [dbo].[bts_sendport].[nID]

INNER JOIN [dbo].[bts_application] ON [dbo].[bts_sendport].[nApplicationID] = [dbo].[bts_application].[nID]

WHERE

[dbo].[bts_sendport_transport].[nvcAddress] <> ''

AND [dbo].[bts_sendport_transport].[nTransportTypeId] = 3

) AS T

GROUP BY ApplicationName, [Name], [URI], [LocationType]

ORDER BY ApplicationName, [Name], [LocationType]

Then all that I had to do is to write is small c# console application which takes in 2 parameters:

1. The name of the SQL server instance which contains my BizTalk databases

2. The name of the BizTalk Management database (e..g. BizTalkMgmtDb)

In my program, I used the Directory.CreateDirectory(path) method which creates all directories (and sub directories) in the specified path.

So finally, the creation of my 165 file locations was done in just a few milliseconds. All I had to do, was to pass in the the correct parameters to my console application.

Enjoy,

Shah