Hi Folks,

I am going to be honest here the default SQL Adapter in Microsoft BizTalk 2006 is a bit of misnomer for me.

Why? If you have a look closely at the Send Handler and Receive Handler for it, you will notice the following:

Send Handler

As you can see above, if you want to INSERT, UPDATE or DELETE data on a SQL Table or Tables you have to provide a XML Document to do it! How? This is not the normal way that we would usually think is it?

Receive Handler

The above seems pretty normal to me, this is normal turf we stepping on here, this we accept with open arms.

Analysis

The receive handler is perfect is it not; we can actually retrieve data from a database by calling a stored procedure, and even provide some parameters.
If we examine the Send Handler, the results are a bit disappointing at first glance. We cannot actually use a stored procedure here to ‘push’ data to SQL in a normal fashion that we would expect to. So we can pull data using a stored procedure but we cannot push data to SQL using a stored procedure directly with the default SQL Adapter, unless we use weird XML files.

Watch this blog: I will be posting a blog later on how you can ‘push’ data to SQL using a stored procedure in a normal fashion, like the Receive Handler, by leveraging the fine features of developing a custom SQL Adapter, so no need for pain staking xml Updategrams and XML stored procedure templates, which are very limiting in certain situations. Personally I do not like the way the default adapter was implemented. Not powerful enough. Updategrams are cool for school and not really much else in my opinion, and why use XML to call a stored procedure, so much overhead, BizTalk 2006 is meant to be a screaming Ferrari not a truck out of the box.

So, for the untrained eye, how do you then actually send data to SQL using the default BizTalk 2006 SQL Adapter?
Well, there is three ways.

• Using XML Updategrams (Part 1)
• Using XML Store Procedure Template (Part 2)
• Custom SQL Adapter (Part 3)

The problem is the first two require an extra step where you need to create a BizTalk Map to actually map out the Updategram or stored procedure.

Before we delve deeper, let’s get a bit intimate with these strange XML Documents. Remember, when I mention the word ‘send’ data, what I am really asking, is how do I UPDATE, INSERT and DELETE data from SQL using the Default BizTalk SQL Adapter.

Imagine we have this table:

 

First let’s insert a record, then update it and then delete it the Default SQL Adapter way using the UpdateGram and then we do the same using a XML Store procedure Template in Part 2.

Updategram that Inserts

Let’s insert a record, since we have an identity field, the CustomerID will be 1 and inserted automatically.

Notice the AFTER element.

 

So the above is the same as:

 

Updategram that Updates

Now, let’s update the record. Notice the BEFORE and AFTER.

 

So the above is the same as:

 

Updategram that Deletes

Ok, we getting bored of this record, lets bin it! Notice the BEFORE has the CustomerID

 

So the above is the same as:

 

Cool, I understand them, how do I use them.

Basically, in a nutshell, you create an Updategram schema and then map your source document to this Updategram.

This is two ways, the manual way and the wizard way. I will show you the wizard way. To keep things consistent the schema I create below is friendly with the Updategrams I showed you above.

• Create a new BizTalk Project, or use an existing one
• Right click the project and choose ‘Add Generated Items’

• Click Add Adapter MetaData

• Click Next

• Setup your connection string

• Fill in the Schema Information, entirely up to you what namespace and root element you use

• Choose the Type of Statement, Updategram

  

• Choose the type

• Click Finish

You can now use this Schema to UPDATE, data from SQL. However, to do this, you will need a way to call the Updategram, we going to do this the BizTalk way, warning this is really not cool.

• First you will need to create a Map to map your data from the Source Document to the Schema created above. This can be tricky, because you might want to create one for an Insert, one for an Update and one for a Delete, so now you see why it is not cool. Think about it folks, we using maps and schemas and pulling out all our weapons of mass destruction in BizTalk to do a simple UPDATE,, INSERT and DELETE, not cool.

One the left we have the normal Customer Schema for a record and on the right the Schema you created using the wizard (This one is an Insert Version, notice no before section). You can probably kill 3 birds with one stone and have one Updategram Schema that has both before and After in it, else the wizard makes you think you need three types, which is not the case. Just leave before or after empty etc. For simplicity I made an Insert one below only, so this schema does not support updates/deletes (No Before section).

• To get this all working, you will need to create a send port and in the SQL Adapter properties for this Send port you specify the schema details for the Updategram, watch:

• We not done! We now need to use that Map, lets really slow down BizTalk and use an Orchestration

• All that’s left is setting up a Receive Port with your Customer Data that needs to be transformed into an UpdateGram and set up the subscriptions.

Tip: You can also create a Receive Port that sucks up Updategrams directly, if you had some sort of system that generated them for you… And that’s the problem the generation of them is such a mission!

Conclusion

I think using Updategrams or XML based store procedure calls by using the Default BizTalk SQL Adapter Send Handler, is well, not cool, and slow.

Not only that, IMAGINE, you want to have a customer table where the data is stored as XML Data Type, your map would need to take the Left Hand Side XML and shove it into an attribute on the Right Side in your BizTalk Map. The best way to do this, is using XSLT and we will trod this ground in Part 2

Also in Part 2, we will find out how using the XML template stored procedure is just as slow and cumbersome and in Part 3, we will find the light at the end of the tunnel with our very own Custom SQL Adapter that works in the normal way we think it should, just the way we want it to.

Why did we go through all this only to find there is a better way to do all this, well I think the best way to appreciate the nice things in life, is to first go through the not so nice experience, and this is the latter, well for me. See you in Part 2 and I hope you anticipate Part 3.

 

Note WCF solved all these issues here!

Hi Folks,

It would be nice to have SQL send email notifications that are HTML based with HTML formatting

Introduction

This is possible because of the following. HTML tables are made of Tags called TD and TR, in SQL we have the FOR XML statement and we can actually cheat the SQL server into thinking that it is creating a XML layout where in fact it is building a HTML table layout.

Below is a sample email sent by SQL Server

Notice the failure column, here we have different colours, if there are errors, it sets the background to Red.

 

SQL Code

Below is the SQL Procedure that does this.

USE [btwf_BizTalkWorkFlowSupport] GO /****** Object:  StoredProcedure [MetaData].[mmit_sp_BizTalkDailyFailureReport]    Script Date: 09/21/2008 09:22:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO –CREATE procedure [MetaData].[mmit_sp_DailyFailureReport] –as ———————— FAILED RECORDS ———————————————– IF (OBJECT_ID(‘#tmpDailyReport’) IS NOT NULL) DROP TABLE #tmpDailyReport CREATE TABLE #tmpDailyReport(    Date varchar( 12 )     Null,    WebServerName         varchar( 150 ) NOT Null,    Location         varchar( 150 ) NOT Null,    cnt_RecsTotal    int Null,    cnt_RecsFailed   int Null ) INSERT INTO #tmpDailyReport(    Date, WebServerName, Location, cnt_RecsTotal,cnt_RecsFailed ) select ‘2008-01-01′,’Amsterdam-Server’,’Amsterdam’,1000,2 union select ‘2008-01-01′,’London-Server’,’London Bridge’,50,0 union select ‘2008-01-01′,’London-Server’,’London West Minster’,80,0 union select ‘2008-01-01′,’Portugal-Server’,’Lisbon’,5,1 DECLARE @tableHTML  NVARCHAR(MAX) ; SET @tableHTML =     N'<H3>Daily Failure Report</H3>’ +       N'<div><H5>Execution Date: <font color="blue">’ + CONVERT(NVARCHAR(30), GETDATE(), 113) + ‘</FONT></H5></div>’ +     N'<table border="1">’ +     N'<tr><th>InsertDate</th><th>WebServerName</th><th>Location</th><th>Total Records</th><th>Failed Records</th>’ +     CAST ( ( SELECT td = Date, ”, td = WebServerName, ”, td = Location, ”, td = cnt_RecsTotal, ”, ‘td/@bgcolor’ = CASE WHEN cnt_RecsFailed > 0 THEN ‘#FF3366’ END, td = cnt_RecsFailed FROM #tmpDailyReport for xml path(‘tr’), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>’ +     N'<br/>’ ; drop table #tmpDailyReport declare @RecipientList as varchar(max) declare @MailqueryStr as varchar(100) declare @FromStr as varchar(100) declare @rslt as varchar(400) set @RecipientList = ‘Romiko@domain.com’ set @FromStr = ‘info@domain.com’ EXEC msdb.dbo.sp_send_dbmail     @profile_name = ‘BizTalk Automated Emails Profile’,     @recipients = @RecipientList,     @body = @tableHTML,     @body_format = ‘HTML’ ,     @subject = ‘Daily Failure Report’ ;

Analysis

The Magic is done here:

SELECT td = Date, ”, td = WebServerName, ”, td = Location, ”, td = cnt_RecsTotal, ”,
‘td/@bgcolor’ = CASE WHEN cnt_RecsFailed > 0 THEN ‘#FF3366’ END,
td = cnt_RecsFailed
FROM #tmpDailyReport
for xml path(‘tr’), TYPE

To understand it, break it down, this query above generates an XML document, that we inject into the HTML variable. Lets see what it looks like with the for XML statement.

I run the Query below, omitting the for XML path statement:

Result:

Now with the XML Statement. I tell the statement that the path is ‘tr’:

 

This means that for every Column a root element called ‘tr’ is created, and then the column data and name is injected. We can then decorate this output into a nice HTML page, as the output is just an HTML Table, and that is what we do here.

We then just concatenate the output from our HTML table generator code to the above statement with a Cast.

Conclusion

With a bit of an imagination, you can generate HTML with SQL Server, since they both tag based languages, so I hope this article will bring colour and life to your SQL email notifications.

Note: You do not need the Type declaration in the FOR XML clause, so

for xml path(‘tr’), TYPE

can be

for xml path(‘tr’)

Just a matter of personnel choice.

Hi Folks,

I notice BizTalk does not like certain Non-Printable characters when dealing with XML data. I use a pipeline component to strip them out of the stream.

Here is the sample code that you can use in a PipeLine component:

using System; using System.Collections.Generic; using System.Text; namespace FileManagement.BOL.Helper {        public  class NonPrintableCharacters     {         public static List<char> charList = new List<char>();         public NonPrintableCharacters ()         {             //Refer to http://nemesis.lonestar.org/reference/telecom/codes/ascii.html             charList.Add((char)Convert.ToInt16("0x01", 16)); //1 Start Of Heading             charList.Add((char)Convert.ToInt16("0x02", 16)); //2 Start Of Text             charList.Add((char)Convert.ToInt16("0x03", 16)); //3 End Of Text             charList.Add((char)Convert.ToInt16("0x04", 16)); //4 End Of Transmission             charList.Add((char)Convert.ToInt16("0x05", 16)); //5 Enquiry, Also known as WRU (Who aRe You), HERE IS, and Answerback             charList.Add((char)Convert.ToInt16("0x06", 16)); //6 Acknowledge             charList.Add((char)Convert.ToInt16("0x07", 16)); //7 Bell             charList.Add((char)Convert.ToInt16("0x08", 16)); //8 Backspace             //Line Feed 0x0A and Horizontal Tab 0x09 is allowed             charList.Add((char)Convert.ToInt16("0x0B", 16)); //11 Vertical Tabulation             charList.Add((char)Convert.ToInt16("0x0C", 16)); //12 Form Feed,             //Carriage Return 0x0D is allowed             charList.Add((char)Convert.ToInt16("0x0E", 16)); //14 Shift Out             charList.Add((char)Convert.ToInt16("0x0F", 16)); //15 Shift In             charList.Add((char)Convert.ToInt16("0x10", 16)); //15 Shift In             charList.Add((char)Convert.ToInt16("0x11", 16)); //17 Device Control 1,Also known as X-ON             charList.Add((char)Convert.ToInt16("0x12", 16)); //18 Device Control 2             charList.Add((char)Convert.ToInt16("0x13", 16)); //19 Device Control 3,Also known as X-OFF             charList.Add((char)Convert.ToInt16("0x14", 16)); //20 Device Control 4             charList.Add((char)Convert.ToInt16("0x15", 16)); //21 Negative Acknowledge             charList.Add((char)Convert.ToInt16("0x16", 16)); //22 Sychronous Idle             charList.Add((char)Convert.ToInt16("0x17", 16)); //23 End of Transmission Block             charList.Add((char)Convert.ToInt16("0x18", 16)); //24 Cancel             charList.Add((char)Convert.ToInt16("0x19", 16)); //25 End of Medium             charList.Add((char)Convert.ToInt16("0x1A", 16)); //26 Substitute             charList.Add((char)Convert.ToInt16("0x1B", 16)); //27 Escape             charList.Add((char)Convert.ToInt16("0x1C", 16)); //28 File Separator             charList.Add((char)Convert.ToInt16("0x1D", 16)); //29 Group Separator             charList.Add((char)Convert.ToInt16("0x1E", 16)); //30 Record Separator             charList.Add((char)Convert.ToInt16("0x1F", 16)); //31 Unit Separator             charList.Add((char)Convert.ToInt16("0x7F", 16)); //127 Delete, Also known as RUB OUT         }         public string ReplaceInvalidChars(string mystring, char newChar)         {             foreach (char c in charList)                 mystring = mystring.Replace(c, newChar);             return mystring;         }     } }

You can call this class from lets say when looping through the stream:

StreamReader reader = new StreamReader(stream, myEncoding) while ((RecordLine = reader.ReadLine()) != null)            {                RecordLine = cleanChars.ReplaceInvalidChars(RecordLine, ‘ ‘);  }

The above will replace the Non-printable characters with a space.

Reason why I use this, is that the XML libraries in .NET support certain Non-Printable characters than BizTalk cannot tolerate.

Hope this is useful. You can do this is a cleaver way with loops on the decimals if you like, made it like this for simplicity.

Hi Folks,

A quick post, when upgrading from MVC 1 to MVC 2 you may get this error in Visual Studio 2008, or when using MVC 2 on a different dev box.

Project Type Not Supported, when trying to open the project file (*.csproj)

very simple to fix.

Open the project file it notepad (or notepad++), and change the guid

<ProjectTypeGuids>{603c0e0b-db56-11dc-be95-000d561079b0};

to

<ProjectTypeGuids>{F85E285D-A4E0-4152-9332-AB1D724D3325}

Then update ALL web.config files, changing the Version to 2.0.0.0, the public key guid stays the same 🙂

<add assembly="System.Web.Mvc, Version=1.0.0.0

To:

<add assembly="System.Web.Mvc, Version=2.0.0.0

Run all Unit Tests

Hope this helps

Aggregator Pattern Pitfalls

One of the features you would expect BizTalk to accomplish is in built BATCHING, where you need to send messages to an external system in Batch Mode, as usual a batching system depends on two variables:

• Maximum Batch Size

OR

• Batching Interval (Time Frame)

Microsoft was kind enough to provide a sample solution for batching. The solution is found here, after BizTalk is installed:

C:\Program Files (x86)\Microsoft BizTalk Server 2006\SDK\Samples\Pipelines\Aggregator

Or

C:\Program Files\Microsoft BizTalk Server 2006\SDK\Samples\Pipelines\Aggregator

Just run the setup.bat file to get it installed on your BizTalk instance. They are even kind enough to provide 4 instance documents to drop in the receive location and then what will happen is the final send location will have them batched into one message, nice hey!

How it works is an envelope is used to batch messages within a pipeline. I highly recommend you play with this SDK, even though it is bugged, you gain good understanding of envelopes in BizTalk.

BUT NOT SO FAST!

What Microsoft fails to tell you is that if you need to batch more than 4 messages you will get ZOMBIES in BizTalk, yes that is right, install the SDK, and create 8 or more instance1.txt files and drop them all at the same time in the receive location, let’s say we drop 80, we should get one message out, instead, you get failed instances in BizTalk, as Zombies are created, how it happens is due to the Orchestration design problem. The orchestration is configured to listen for messages, the problem is when it is finalizing the final batch message it is possible that a new message from the BizTalk message box can sneak into the Orchestration receive handler, but the final message is already created, so this new message that crept into the process becomes a zombie and you get stuck messages in the BizTalk message box!

Many people on the net use a listen shape, and use a timer to manage batching, Microsoft’s SDK approach is a loop and use MAX BATCH SIZE, and either method will cause zombie records. Look at the orchestration below:

 

When the loop ends, and processing continues, it is possible that more messages from the message box arrive! This is what causes stuck messages in the BizTalk Message box. Errors will be like this:

0xC0C01B4C The instance completed without consuming all of its messages. The instance and its unconsumed messages have been suspended.

So as far as I am concerned Microsoft have some serious work to do on BizTalk to address this problem, I feel that Batching is core requirement for any workflow system, and they should address this, if you read their articles they mention the problem, BUT DO NOT PROVIDE A SOLUTION.

There are solutions, we use a MSMQ or SQL table to manage batching, but I feel this is not a nice and elegant approach.

Summary

Microsoft need to address this problem in BizTalk or provide the community with a Batching Adapter, it is about time they get this done, this problem has been around now for far too long!

http://msdn.microsoft.com/en-us/library/bb203853.aspx

http://msdn.microsoft.com/en-us/library/aa561361.aspx

http://msdn.microsoft.com/en-us/library/ms942189.aspx

Thanks Microsoft for providing us with a flawed SDK sample that only works when you batch small message numbers (Less than 8)! Please take this opportunity to please the BizTalk community and provide us with a professional batching adapter.

What interested me the most or amuses me is the various authors on BizTalk books that have discussed this pattern, and not once mentioned the bug, and reviewing their solution has shown to also have the bug, but then again, most of the examples for BizTalk on educational resources are always with small batch sizes like a flight agency/hotel booking company, where you have a convoy of max 2 messages, not one of them have discussed enterprise batching solutions.

Hopefully we see some improvements in this regard soon.

Hi Folks,

The built in Split function in C# is cool to split up a delimited string, or record when reading text data from files etc, but what happens when you need to split a string with text qualifiers in it like this:

Romiko, Derbynew, 29, "52 SurfSide Street, Durban, South Africa"

You going to run into issues when using the C# built in string split function.

I checked out a number of places on the net and many of the samples are SLOW. So I thought it would be best to create a low level version, that is extremely fast.

Here it is:

 

I ran the above with 10 000 or so records and it was completed within 2 seconds or so. Some slow versions on the net that I found took over 6-7 minutes:

Here are some slow ones that you may run into on the net.

 

 

 

So I hope you have fun with split functions, and if you in the mood to make other custom functions, I am keen to see them!

 

Introduction

I recently published a blog in regards to developing a custom application that will configure IIS using Directory Services. The tool is capable of:

• Creating Application Pools
• Creating Virtual Directories
• Creating Web Sites
• Configuring settings for the above (Application Pool Identity, Anonymous User, Virtual Directory Application Name)

To be honest, I was not entirely happy with this tool, since it would not work on Vista or later versions of IIS. Another problem, was that the XML file holding the configuration was my own ‘Language’ of expressing how to configure IIS and is not a standard, so could be hard for administrators to learn if I depart from the company, so we need something that a community supports.

A colleague of mine (Chris Hagens) introduced me to MSBuild, and I was blown away by this tool and how easy it is to use. This article will introduce a simple build script that will get you going so that you can blow the mind of your administrator when the next deployment schedule is nigh.

Before we continue, please take a moment to read this article, I certainly found it helpful in fast tracking my learning curve, I enjoy to understand processes instead of trying to hack. So put a sign on the door "Do not disturb" and grab a cup of tea and lets get down and dirty!

Patrick Smacchia: http://www.codeproject.com/KB/books/msbuild.aspx

Internet Information Server

Scenario

We are implementing a SOA (Service Orientated Architecture) solution which requires various web services to be deployed on various servers and different environments (Pre-Production, Production, Test and Development).

NTFS permissions will also be created and active directory account will be added to a group.

Solution

We going to geek it to the max and create a simple MSBuild script that will run at configure the web services automatically for us. There are many ways to deploy zipped applications, so in this solution, I want to keep it simple and assume you have a separate script to unzip the web service web files e.g asmx, web.config and dll’s to the file system.

The solution consists of two files:

• WebServers.cmd
• WebServices.build

There is also assemblies and schemas used:

• Microsoft.Sdc.Common.tasks
• MSBuild.Community.Tasks.Targets
• Microsoft.Sdc.Tasks.BizTalk.dll
• MSBuild.Community.Tasks.dll

I have created a zip file that you can download.

Download

http://grounding.co.za/files/folders/documents/entry1841.aspx

Prerequisite

.Net Framework

The prerequisite for this to work is the .NET Framework version 2 is installed, just the runtime

Here is a link for version 2 on a x86 platform

 http://www.microsoft.com/downloads/details.aspx?familyid=0856eacb-4362-4b0d-8edd-aab15c5e04f5&displaylang=en

if you on 64-bit windows, then download

http://www.microsoft.com/downloads/details.aspx?familyid=B44A0000-ACF8-4FA1-AFFB-40E78D788B00&displaylang=en

Libraries, XSD and import files

• Microsoft.Sdc.Common.tasks
• MSBuild.Community.Tasks.Targets
• Microsoft.Sdc.Tasks.dll
• MSBuild.Community.Tasks.dll

Note: If you run this on XP you may not get Application Pools! IIS 6.0 and higher uses Application Pools.

Analysis

Main Batch File

Here is the main batch file, it’s sole purpose is to pass the correct environment variable to the MSBuild tool, so it knows which environment it is building:

WebServices.cmd

———————————————————————————————-

@echo off
set /P ENVIRONMENT="Choose environment (DEV, TEST, PREP, PROD) : "
IF %ENVIRONMENT% == DEV GOTO deploy
IF %ENVIRONMENT% == TEST GOTO deploy
IF %ENVIRONMENT% == PREP GOTO deploy
IF %ENVIRONMENT% == PROD GOTO deploy
ECHO Unknown environment
GOTO end

:deploy
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\msbuild.exe WebServices.build /property:ENVIRONMENT=%ENVIRONMENT%

:end
pause

———————————————————————————————-

The first thing we need to do is store the answer to the  question into an environment variable, in this case the question or prompt is going to be "Choose environment…."

The set /P switch allows you to set the value of a variable to a line of input entered by the user.  Displays the specified promptString before reading the line of input.  The promptString can be empty.

Once we have successfully we can now call the msbuild utility and parse it the correct argument for the environment.

"msbuild.exe WebServices.build /property:ENVIRONMENT=%ENVIRONMENT%"

WebServices.Build

———————————————————————————————-

<Project DefaultTargets="All"  xmlns="http://schemas.microsoft.com/developer/msbuild/2003" >
    <Import Project="C:\MSBUILD\Tools\Microsoft.Sdc.Common.tasks" />
    <Import Project="C:\MSBUILD\Tools\MSBuild.Community.Tasks.Targets"/>
    <!– Set the application name as a property –>
    <PropertyGroup Condition="$(ENVIRONMENT)==’DEV’">
        <ServerName>.</ServerName>
        <!– Gateway settings –>
        <GatewayAppPool>MMITGatewayPool</GatewayAppPool>
        <GatewayIdentityUsername>Administrator</GatewayIdentityUsername>
        <GatewayIdentityDomain>guru-f09deb3e</GatewayIdentityDomain>
        <GatewayIdentityFQUser>$(GatewayIdentityDomain)\$(GatewayIdentityUsername)</GatewayIdentityFQUser>
        <GatewayIdentityPassword>password</GatewayIdentityPassword>
        <GatewayBackupFolder>C:\MSBuild\Example\Gateway\Backup</GatewayBackupFolder>
        <GatewayVirtualPath>C:\MSBuild\Example\Gateway\WebServices</GatewayVirtualPath>
        <GatewayVirtualDirName>Gateway.WebServices</GatewayVirtualDirName>
    </PropertyGroup>
    <PropertyGroup Condition="$(ENVIRONMENT)==’TEST’">
        <ServerName>.</ServerName>
        <!– Gateway settings –>
        <GatewayAppPool>MMITGatewayPool</GatewayAppPool>
        <GatewayIdentityUsername>_SA_WebService</GatewayIdentityUsername>
        <GatewayIdentityDomain>TEST</GatewayIdentityDomain>
        <GatewayIdentityFQUser>$(GatewayIdentityDomain)\$(GatewayIdentityUsername)</GatewayIdentityFQUser>
        <GatewayIdentityPassword>PassworD</GatewayIdentityPassword>
        <GatewayBackupFolder>c:\backup</GatewayBackupFolder>
        <GatewayVirtualPath>C:\Gateway\WebServices</GatewayVirtualPath>
        <GatewayVirtualDirName>Gateway.WebServices</GatewayVirtualDirName>
    </PropertyGroup>
    <PropertyGroup Condition="$(ENVIRONMENT)==’PREPROD’">
        <ServerName>.</ServerName>
        <!– Gateway settings –>
        <GatewayAppPool>MMITGatewayPool</GatewayAppPool>
        <GatewayIdentityUsername>_SA_WebService_PP</GatewayIdentityUsername>
        <GatewayIdentityDomain>TEST</GatewayIdentityDomain>
        <GatewayIdentityFQUser>$(GatewayIdentityDomain)\$(GatewayIdentityUsername)</GatewayIdentityFQUser>
        <GatewayIdentityPassword>pASSWORd</GatewayIdentityPassword>
        <GatewayBackupFolder>c:\backup</GatewayBackupFolder>
        <GatewayVirtualPath>C:\Gateway\WebServices</GatewayVirtualPath>
        <GatewayVirtualDirName>Gateway.WebServices</GatewayVirtualDirName>
    </PropertyGroup>
    <PropertyGroup Condition="$(ENVIRONMENT)==’PROD’">
        <ServerName>.</ServerName>
        <!– Gateway settings –>
        <GatewayAppPool>MMITGatewayPool</GatewayAppPool>
        <GatewayIdentityUsername>_SA_WebService</GatewayIdentityUsername>
        <GatewayIdentityDomain>PROD</GatewayIdentityDomain>
        <GatewayIdentityFQUser>$(GatewayIdentityDomain)\$(GatewayIdentityUsername)</GatewayIdentityFQUser>
        <GatewayIdentityPassword></GatewayIdentityPassword>
        <GatewayBackupFolder>c:\backup</GatewayBackupFolder>
        <GatewayVirtualPath>C:\Gateway\WebServices</GatewayVirtualPath>
        <GatewayVirtualDirName>Gateway.WebServices</GatewayVirtualDirName>
    </PropertyGroup>

   <Target Name="AppPool" >
        <Prompt Condition="$(ENVIRONMENT) == ‘PROD’" Text="Enter the application pool password">
            <Output TaskParameter="UserInput" PropertyName="GatewayIdentityPassword"/>
        </Prompt>
        <Message Text="Creating Gateway Application Pool" />
        <Web.AppPool.Create ContinueOnError="true" AppPoolName="$(GatewayAppPool)" Identity="$(GatewayIdentityFQUser)" Password="$(GatewayIdentityPassword)" IdentityType="3" PeriodicRestartTime="12"/>
    </Target>

    <Target Name="VirtualDirectory">
        <Message Text="Creating Gateway Virtual Directory" />
        <Web.WebSite.DeleteVirtualDirectory VirtualDirectoryName="MMIT.Gateway.WebServices" />
        <Web.WebSite.CreateVirtualDirectory AppPoolId="$(GatewayAppPool)" AppCreate="true" WebAppName="$(GatewayVirtualDirName)" Path="$(GatewayVirtualPath)" VirtualDirectoryName="$(GatewayVirtualDirName)" />
    </Target>

    <Target Name="FolderPermissionsBackup">
        <MakeDir Directories="$(GatewayBackupFolder)"/>
        <Exec Command="cacls $(GatewayBackupFolder)  /G $(GatewayIdentityFQUser):F /T /E" />
        <Exec Command="cacls c:\windows\temp  /G $(GatewayIdentityFQUser):F /T /E" />
    </Target>

    <Target Name="ASPWorkerProcess">
        <ActiveDirectory.Group.AddUser ContinueOnError="true" GroupName="IIS_WPG" GroupMachine ="$(ServerName)" UserName="$(GatewayIdentityUsername)" UserDomain ="$(GatewayIdentityDomain)" />
    </Target>
    <Target Name="All">
        <CallTarget Targets="AppPool" />
        <CallTarget Targets="VirtualDirectory" />
        <CallTarget Targets="FolderPermissionsBackup" />
        <CallTarget Targets="ASPWorkerProcess" />
    </Target>
</Project>

———————————————————————————————-

XML Declarations and Imports

<Project DefaultTargets="All"  xmlns="http://schemas.microsoft.com/developer/msbuild/2003" >
    <Import Project="C:\MSBuild\Tools\Microsoft.Sdc.Common.tasks" />
    <Import Project="C:\MSBuild\Tools\MSBuild.Community.Tasks.Targets"/>
    <!– Set the application name as a property –>

This is an advanced XML file that actually conforms to an XSD, which is referenced in this namespace:

"http://schemas.microsoft.com/developer/msbuild/2003"

I recommend you reference it, it will allow you to use type safety when writing the XML configuration file, assuming you using an XML editor (notepad is not recommended, your eyes will go dizzy). Notice my files are located in my C:\MSBuild\Tools\ folder, you can modify this and choose your path.

Property Group

This section in the XML is very powerful, here you can define variables, like when writing a class, and then reference them, what is really nice you can use conditional statements to initialise the properties depending on a state of a variable e.g.

Condition="$(ENVIRONMENT)==’DEV’

In my example, I have properties to store for Username, Password, Domain and also the fully qualified username e.g. Domain\UserName

<PropertyGroup Condition="$(ENVIRONMENT)==’DEV’">
        <ServerName>.</ServerName>
        <!– Gateway settings –>
        <GatewayAppPool>MMITGatewayPool</GatewayAppPool>
        <GatewayIdentityUsername>Gateway</GatewayIdentityUsername>
        <GatewayIdentityDomain>DEV</GatewayIdentityDomain>
        <GatewayIdentityFQUser>$(GatewayIdentityDomain)\$(GatewayIdentityUsername)</GatewayIdentityFQUser>
        <GatewayIdentityPassword>mmitdev</GatewayIdentityPassword>
        <GatewayBackupFolder>c:\backup</GatewayBackupFolder>
        <GatewayVirtualPath>C:\Code\MMIT.Gateway\MMIT.Gateway.WebServices</GatewayVirtualPath>
        <GatewayVirtualDirName>MMIT.Gateway.WebServices</GatewayVirtualDirName>
</PropertyGroup>

Target Group

In this section you put the implementation of your scripts, like running commands.

I ran cacls.exe which confgures NTFS permissions for a backup folder and various other tasks, many of which are in libraries, the command "Web.WebSite.CreateVirtualDirectory" actually calls the dll referenced by the XML and passes the parameters in.

Below is a sample target Action which adds a user to a group:

    <Target Name="ASPWorkerProcess">
        <ActiveDirectory.Group.AddUser ContinueOnError="true" GroupName="IIS_WPG" GroupMachine ="$(ServerName)" UserName="$(GatewayIdentityUsername)" UserDomain ="$(GatewayIdentityDomain)" />
    </Target>

Running the tool

Double click WebServices.cmd

Type in DEV (The other environments will fail, unless you configure the properties for them)

 

Output of Sample Run

—————————————————————–

Choose environment (DEV, TEST, PREP, PROD) : DEV
Microsoft (R) Build Engine Version 2.0.50727.832
[Microsoft .NET Framework, Version 2.0.50727.832]
Copyright (C) Microsoft Corporation 2005. All rights reserved.

Build started 20/09/2008 10:03:29.
__________________________________________________
Project "C:\MSBuild\WebServices.build" (default targets):

Target All:
    Target AppPool:
        Creating Gateway Application Pool
        Creating app pool "MMITGatewayPool".
        MSBUILD : warning : A task error has occured.
        MSBUILD : warning : Message             = App Pool already exists.
        MSBUILD : warning : MachineName         = localhost
        MSBUILD : warning : AppPoolName         = MMITGatewayPool
        MSBUILD : warning : IdentityType        = 3
        MSBUILD : warning : Identity            = guru-f09deb3e\Administrator
        MSBUILD : warning : Password            = password
        MSBUILD : warning : IdleTimeout         = 20
        MSBUILD : warning : PeriodicRestartTime = 12
        MSBUILD : warning : WorkerProcesses     = 1
        MSBUILD : warning : RestartSchedule     = <String.Empty>
        MSBUILD : warning : RequestQueueLimit   = 1000
        MSBUILD : warning :
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.
EnsureAppPool()
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.
Save()
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.Web.AppPool.Create.Interna
lExecute()
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.TaskBase.Execute()
        MSBUILD : warning : The system cannot find the path specified.
        MSBUILD : warning :
        MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.Bind(B
oolean throwIfFail)
        MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.Bind()

        MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.get_Is
Container()
        MSBUILD : warning :    at System.DirectoryServices.DirectoryEntries.Chec
kIsContainer()
        MSBUILD : warning :    at System.DirectoryServices.DirectoryEntries.Add(
String name, String schemaClassName)
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.
EnsureAppPool()
        The previous error was converted to a warning because the task was calle
d with ContinueOnError=true.
        Build continuing because "ContinueOnError" on the task "Web.AppPool.Crea
te" is set to "true".
    Done building target "AppPool" in project "WebServices.build".
    Target VirtualDirectory:
        Creating Gateway Virtual Directory
        Deleting virtual directory "MMIT.Gateway.WebServices".
        Creating virtual directory "Gateway.WebServices".
    Target FolderPermissionsBackup:
        cacls C:\MSBuild\Example\Gateway\Backup  /G guru-f09deb3e\Administrator:
F /T /E
        processed dir: C:\MSBuild\Example\Gateway\Backup
        cacls c:\windows\temp  /G guru-f09deb3e\Administrator:F /T /E
        processed dir: c:\windows\Temp
        processed file: c:\windows\Temp\avg8info.id
        processed file: c:\windows\Temp\DMI6DC.tmp
        processed file: c:\windows\Temp\ehprivjob.log
        processed file: c:\windows\Temp\ehprivjob1.log
        processed file: c:\windows\Temp\MpSigStub.log
        processed file: c:\windows\Temp\WinSAT_DX.etl
        processed file: c:\windows\Temp\WinSAT_KernelLog.etl
        processed file: c:\windows\Temp\WinSAT_StorageAsmt.etl
    Target ASPWorkerProcess:
        MSBUILD : warning : A task error has occured.
        MSBUILD : warning : Message             = The specified domain either do
es not exist or could not be contacted.
        MSBUILD : warning :
        MSBUILD : warning : GroupMachine[0]     = .
        MSBUILD : warning : EnsureUserIsInGroup = False
        MSBUILD : warning : UserName            = Administrator
        MSBUILD : warning : GroupName[0]        = IIS_WPG
        MSBUILD : warning : UserDomain          = guru-f09deb3e
        MSBUILD : warning :
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.ActiveDirect
ory.User.Exists(String username, String domainName)
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.ActiveDirectory.Group.AddU
ser.InternalExecute()
        MSBUILD : warning :    at Microsoft.Sdc.Tasks.TaskBase.Execute()
        The previous error was converted to a warning because the task was calle
d with ContinueOnError=true.
        Build continuing because "ContinueOnError" on the task "ActiveDirectory.
Group.AddUser" is set to "true".
    Done building target "ASPWorkerProcess" in project "WebServices.build".
Done building target "All" in project "WebServices.build".

Done building project "WebServices.build".

Build succeeded.
MSBUILD : warning : A task error has occured.
MSBUILD : warning : Message             = App Pool already exists.
MSBUILD : warning : MachineName         = localhost
MSBUILD : warning : AppPoolName         = MMITGatewayPool
MSBUILD : warning : IdentityType        = 3
MSBUILD : warning : Identity            = guru-f09deb3e\Administrator
MSBUILD : warning : Password            = password
MSBUILD : warning : IdleTimeout         = 20
MSBUILD : warning : PeriodicRestartTime = 12
MSBUILD : warning : WorkerProcesses     = 1
MSBUILD : warning : RestartSchedule     = <String.Empty>
MSBUILD : warning : RequestQueueLimit   = 1000
MSBUILD : warning :
MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.EnsureAp
pPool()
MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.Save()
MSBUILD : warning :    at Microsoft.Sdc.Tasks.Web.AppPool.Create.InternalExecute
()
MSBUILD : warning :    at Microsoft.Sdc.Tasks.TaskBase.Execute()
MSBUILD : warning : The system cannot find the path specified.
MSBUILD : warning :
MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.Bind(Boolean t
hrowIfFail)
MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.Bind()
MSBUILD : warning :    at System.DirectoryServices.DirectoryEntry.get_IsContaine
r()
MSBUILD : warning :    at System.DirectoryServices.DirectoryEntries.CheckIsConta
iner()
MSBUILD : warning :    at System.DirectoryServices.DirectoryEntries.Add(String n
ame, String schemaClassName)
MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.Web.AppPool.EnsureAp
pPool()
MSBUILD : warning : A task error has occured.
MSBUILD : warning : Message             = The specified domain either does not e
xist or could not be contacted.
MSBUILD : warning :
MSBUILD : warning : GroupMachine[0]     = .
MSBUILD : warning : EnsureUserIsInGroup = False
MSBUILD : warning : UserName            = Administrator
MSBUILD : warning : GroupName[0]        = IIS_WPG
MSBUILD : warning : UserDomain          = guru-f09deb3e
MSBUILD : warning :
MSBUILD : warning :    at Microsoft.Sdc.Tasks.Configuration.ActiveDirectory.User
.Exists(String username, String domainName)
MSBUILD : warning :    at Microsoft.Sdc.Tasks.ActiveDirectory.Group.AddUser.Inte
rnalExecute()
MSBUILD : warning :    at Microsoft.Sdc.Tasks.TaskBase.Execute()
    2 Warning(s)
    0 Error(s)

Time Elapsed 00:00:02.89
Press any key to continue . . .

—————————————————————–

Download

http://grounding.co.za/files/folders/documents/entry1841.aspx

Conclusion

 

I think this article gives enough substance to get you going in creating deployment scripts that are kick ass. Play with it, and remember this is designed for IIS 6.0 and above. I did not package in a sample web service file, but I am sure you folks can test it with a real virtual directory, my sample for the dev environment is empty, to keep things simple. Remember, when you run the tool to type DEV and dev etc, the prompts are case sensitive.

This tool is really fun to play with and adds an extra edge to your applications at deployment time. In Part 2, we will take this tool to the limits and automate BizTalk 2006 deployments, which I can assure you, requires some super Geeking!

Hi Folks,

In this discussion, we will look at how we can call static methods with PowerShell.

 

Call Static Methods

Allot of the System namespaces are already loaded in PowerShell. So for example the following command can be used to get the current datetime.

So we issue the following command:

$MyDate = [System.DateTime]::Now

 

Notice the above is similar to using reflection in .NET to load an assembly:

[System.Reflection.Assembly]::LoadFrom("..\mydll") or calling other methods like assembly loads:

[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

You can of course run endless number of static methods:

Try this one:

[System.Console]::get_backgroundcolor()

Intellisense

We need a way to enumerate .NET assemblies, like we do in Visual Studio!

Before we get started, I like to use PowerTab for intellisense. You can download it here:

http://thepowershellguy.com/blogs/posh/pages/powertab.aspx

Once I download it and before I run the setup, I need to trust the script, I do it the lazy way and trust all scripts, then afterwards will set it back to default policy:

Set-ExecutionPolicy unrestricted

Later after the install you can set it back to normal:

Set-ExecutionPolicy Restricted

You can read more about signing with this command:

get-help about_signing

 

Once I run this, I double click the setup from PowerTab, you will be presented with this dialog box:

I press enter to kick off the install.

I answer the various questions during the install, usually the default will suffice:

Here is the final screen, once PowerTab is installed:

When I close and reopen PowerShell, I now have PowerTab for Intellisense, this can be useful for seeing .NET assemblies using the TAB key.

YAY! Now check it out when I press the TAB key when looking for a .NET class to script:

I can now easily write some f^%& cool scripts, so who’s your Daddy?

Object Instantiation

Lets try an instantiate a class. So lets assume we have no clue how to do this, use this command to get the help we need:

Get-Help *

This will display a list of help topics, lets see if we can find something on object instantiation. I see new_object, this seems like something we will need:

PS C:\Documents and Settings\romiko> Get-Help new-object

NAME

New-Object

SYNOPSIS

Creates an instance of a .Net or COM object.

SYNTAX

New-Object [-typeName] <string> [[-argumentList] <Object[]>] [<CommonParameters>]

New-Object [-comObject] <string> [-strict] [<CommonParameters>]

DETAILED DESCRIPTION

Creates an instance of a .Net or COM object. You specify either the type of a .Net class or a Programmatic Identifi

er (ProgID) of a COM object. By default, you type the fully-qualified name of a .Net class and the cmdlet returns a

reference to an instance of that class. To create an instance of a COM object, use the ComObject parameter and spe

cify the ProgID of the object as its value.

RELATED LINKS

Compare-Object

Select-Object

Sort-Object

ForEach-Object

Group-Object

Measure-Object

Tee-Object

Where-Object

REMARKS

For more information, type: "get-help New-Object -detailed".

For technical information, type: "get-help New-Object -full".

PS C:\Documents and Settings\romiko>

 

Ok, so from here let us try and instantiate a .NET Object.

Imagine we have a text file with XML:

————myXML.xml———————-

<Person>
<Name>Romiko</Name>
<Surname>Van De Dronker</Surname>
</Person>

———————————————–

Lets say we want to load this into the DOM as an instance of the XMLDocument class.

First we create an instance:

$myInstance = new-object System.Xml.XmlDocument

This is what we can do is load the XML from a file:

$myInstance.Load("c:\myXML.xml")

Now we can display the XML from the object

$myInstance.Person

Here is the output:

You can even see other properties like:

$myInstance.get_InnerXml()

returns a string

<Person><Name>Romiko</Name><Surname>Van De Dronker</Surname></Person>

Other useful things you can do is see members like when you reflect objects:

[System.Xml.XmlDocument] | get-member

Conclusion

Well, I hope this gave you an insight into what we can do with PowerShell, we can pretty much do what we want with the system, and there is also allot of help features and even intellisense, if you take the time to download and install PowerTab, it helps allot when coding against assemblies, you can even use reflection here and load other assemblies and dll’s!

Have fun with it, and I see you folks next time, when we Geek it up with BizTalk/SharePoint and PowerShell.

Cheers

Hi Folks,

This article is going to give a brief demonstration on how useful PowerShell can be, not only for administrators but also for developers. One of the really cool things with PowerShell is the ability to call static classes in .NET and also instantiate objects from the command console. Sometime if I need to find something deep in the object model of BizTalk and lately even with SharePoint, PowerShell can then be used to quickly check out the hidden agenda’s!

Recently, I wanted to run a test case, where I needed to migrate documents from MOSS 2003 to MOSS 2007. The problem is that I needed to keep the original file names of the migrated documents and do test runs on my virtual machine for all 80000 documents. I decided the best way forward to generate the test data, was use SQL to get a list of the filenames and then use PowerShell to recreate the files with the same filenames but just have small garbage in it, so that my VM will not run out of space when I do a dry run of the migration tool.

So, to start with, I have a text file with the following in it:

—— MyTextFile.txt ——

FileNamea.txt
FileNameb.txt
FileNamec.txt

—————————–

If you feel like using an old dos command to make a file with text:

In the above, you use F6 to save the file, which is the ^Z symbol.

What we are going to do, is use PowerShell to loop through the text file and create 1kb files for me with the same name.

First you need to download PowerShell from here:

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx

You can then open the console, by navigating to the shortcut in the start menu.

Then you run the following command:

type MyTextFile.txt | foreach($_){New-Item -name $_ -itemType file ; "romiko" > $_ }

Here we use the type command to output the contents of the file to standard output in the console. Then we PIPE the output into a foreach statement, where $_ is the standard Console.ReadLine. We then have an action to create a New Item of type File. Once the file is created we redirect a string called "romiko" into the newly create file > $_. This is similar to using this command dir /w > directorylist.txt

Here is the output

Notice it says the file is 0 Length, do not believe it, run another dir command:

This is the first of many blogs that I will be writing in regards to PowerShell. When I have a moment I will demonstrate the use of PowerShell for checking things out in the SharePoint and BizTalk object model, until then, enjoy the world of powerful scripting.

Now, who said that Windows Shell was not as Powerful as Unix? Thanks to Joe Capka for introducing me to this wonderful tool! You can read Joe Capka’s blogs here:

http://jcapka.blogspot.com/

Regards

Romiko

Hi Folks,

When you have developed an Orchestration with different possible routes and branches, it might be useful to know which branches are executing the most. I certainly dislike Orchestrations but for this solution, I had to use one due to legacy system design L, so I got a good excuse this time round!

I am using an orchestration here that calls many other orchestrations (BAD PRACTICE, so always avoid it if possible).

You can use a tool called

BizTalk Server 2006 Orchestration Profiler v1.1.1

Download the Code from:

http://www.codeplex.com/BiztalkOrcProfiler/Release/ProjectReleases.aspx?ReleaseId=6375

If you running it on 64 bit machine, change the config of the file for the program files directory!

C:\Program Files\Microsoft Services\BizTalk 2006 Orchestration Profiler

In 64 bit it is C:\Program Files (x86) Microsoft Services\BizTalk 2006 Orchestration Profiler

 

So just fix the configuration file path:

Microsoft.Sdc.OrchestrationProfiler.exe.config

 

Ok, so now we ready to analyze the orchestration. Go to the Start Menu to start the program

Click List Orchestrations

Click the Orchestrations you want to profile. Then Click generate report.

 

If you get this error:

 

 

Then you need to go to the config file I spoke about:

Microsoft.Sdc.OrchestrationProfiler.exe.config

 

And change the path to the location where the hhc.exe file is locate don your computer, on mine it is at:

<appSettings>

<add key="HelpCompilerLocation" value="C:\Program Files\HTML Help Workshop\hhc.exe" />

<add key="ShowHotSpots" value="0" />

</appSettings>

The HHC.EXE file is used to create a chm file which is windows help file, nice to use to page through and stuff like that.

You can download it and install it at:

http://www.microsoft.com/downloads/details.aspx?FamilyID=00535334-c8a6-452f-9aa0-d597d16580cc&DisplayLang=en

 

 

I chose to install it in the same location as the default location in the config fiel above J

 

After installing this etc, I went back the program and generated the report for my orchestration:

It created a report on my c:\ BizTalk Orchestration Profile Report.chm

 

 

 

So from the above you can see that this particular orchestration catches general exceptions and not other types of exceptions, so I know from the web service errors that general exceptions are being thrown in 100% of the cases. I use this example as a example where I ran a test cycle on 200 records and wanted to know where in my orchestration the 38 records out of the 200 failed, I wanted to know if it was a combination of different type of errors, to get an idea of what error handling the external web service may need, since the external web service I call, always returns a False or True (Not a cool way to do things, always develop web services to give decent error messages!)

So from here, I can see the general flow of messages and what exception types occur and make additional improvements to the design.

Also, I can see what takes long.

Also which shapes are a loser.

Hope you also get a chance to profile your orchestrations and see how they are working!

Good Luck, and remember, avoid orchestrations when you can, they the easy way out and can cause an overhead if used in the incorrect manner. I have seen on many occasions people using them in an Object Orientated Manner, not good, the orchestration in this example is a good case to go by, it calles other orchestrations that should actually be treated as objects not orchestrations.

Cheers