Excel application development on 32 Vs 64 bit Operating System

Almost everybody had to present some kind of a written project growing up. One of my first wow moments with MS Office came when I learnt how to produce graphs based on the data existing in an Excel file. Hats off to who ever said “Pictures are a thousand words”, my presentations became so much more lively and interesting to present.

Now that I am all grown up and all, Excel still saves my day almost regularly. BUT, what might have been a god sent gift growing up, has become a bit of a pain lately while working with Excel files on 64 bit platforms as part of software development.

The biggest challenge to me is the compatibility with 32 bit and 64 bit adapters. Almost every time I wrote a new app that uses Excel as a data source, be it in Java, C#, BizTalk, SSIS, etc I always got stuck at some weird error.

Recently I was writing a quick SSIS project to manipulate data from an Excel file and go this error:
Error: 0xC020801C at Data Flow Task, Excel Source [114]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

A couple minutes after running through this error I realized I was on a 64 bit OS and had to ensure my SSIS project was not looking for a 64 bit Excel adapter. This is what I had to do to fix this SSIS issue:
· Go to solution explorer
· Right click on project
· Select properties
· Go to debugging tab
· Set Run64BitRuntime to false

How to defragment a file or folder instead of the whole drive

Everybody is aware of using Disk Defragmenter that comes out of the box with a Microsoft OS to defragment broken files across a given drive. Depending on how large or how bad this disk is, it could take hours to defragment a given drive.

There are multiple situations where you might want to defrag just a folder or a network share, assuming u have admin permissions to do that (No this can’t be used as a prank to mess with your neighbor).

In my case, I was running a BitTorrent on my machine for a while and observed that most of my fragments are under a single folder. I did not want to defrag the whole drive just for this, so I used to wait until I just moved/deleted the contents under that folder.

Recently I came across something simple, yet unpublicized utility from Microsoft which does exactly this. I wonder why people install or pay for some third party software vendor that does this.

Here is how you do this for yourself
Steps:
1. Download contig.exe from http://technet.microsoft.com/en-us/sysinternals/bb897428
2. Create a Defrag.BAT file with the following contents:

DEL Defrag.log
echo Start Time: %time% >> Defrag.log
defrag.exe E:\folder -V >> Defrag.log
echo End Time: %time% >> Defrag.log

Note: in the above BAT file, E:\folder is the folder to be defragged.

Blog somnia is over

It has been over 2 years since i last blogged. It is an understatement if i said i had an insomnia about my blog. It was more of a sabatical phase(aka marriage), where i was busy absorbing the world around me.

Also, i realised my blog was host to spam comments and ads ranging from viagra to bald hair cure. Apologies for letting such waste of digital media. I promise to do a better job this time :-)

Hopefully i get a chance to share more meaningful information with my friends going forward.

Distinguished Fields vs. Promoted Properties in Biztalk

In Biztalk you can promote a property as either distinguished or promoted properties. Choosing what to use is tricky.

Distinguished fields: In general use Distinguished fields if you want to just use them in Orchestration

• No restriction on field length
• No participation in Routing
• No seperate property Schema
• Not accesible by std pipeline components

Promoted Properties: These are generally used with Routing

• Used in publish and subscribe in the Msgbox
• Field Restictions ( 255)
• Seperate Property schema
• Restricted in the XSD types support compared to distinguished properties

Synopsis: Use Distinguish properties as they are lightweight unless you need them for any special stuff like Routing ,Tracking ,Correlation or custom pipeline behaviour

Using Envelope Schema in Biztalk

What is Envelope Schema: An envelope schema is a special type of XML schema. Envelope schemas are used to define the structure of XML envelopes, which are used to wrap one or more XML business documents into a single XML instance message. When you define an XML schema to be an envelope schema, a couple of additional property settings are required, depending on such factors as whether there are more than one root record defined in the envelope schema

As most of you aware of the fact that you can either

• Split a single message into multiple messages using Receive pipeline.
• Append multiple mesages into a single message using a Send Pipeline

In both the cases you would need to use an "Envelope Schema" to group them together. Following are instructions how to configure the Envelope schema to achieve the above processes.

Splitting a single message into multiple messages:
You need to create an Orders envelope schema, and Order document schema for that.

Steps:

1. Create a new BizTalk project
2. Add the Order document schema with OrderId and Date elements
3. Add the Orders envelope schema with only root element Orders
4. Click on the Schema node and set its "Envelope" property to true
5. Click on the Orders node, and click on the body xPath property, choose Orders and click ok.
6. Deploy the project
7. Create a receive location and choose XmlReceive pipeline
8. Open the pipeline properties dialog, and set envelope schema to the Orders schema, and document schema to Order schema

Append multiple messages into a single message: You need to create an Orders envelope schema, and Order document schema for that.

Steps:

1. Create a new BizTalk project
2. Add the Order document schema with OrderId and Date elements
3. Add the Orders envelope schema with only root element Orders
4. Click on the Schema node and set its "Envelope" property to true
5. Click on the Orders node, and click on the body xPath property, choose Orders and click ok.
6. If you are invoking a send pipeline component within an Orch expression: Open the pipeline properties dialog, and set envelope schema to the Orders schema, and document schema to Order schema
7. Deploy the project

Compacting a Dynamic VHD image

One of the nicest features of using Dynamic VHDs on Virtual Server 2005 is that they grow as needed, so system administrators can optimize the disk space usage between several VMs. Dynamic VHDs grow automatically, but they don’t shrink automatically. In order to shrink them, you need to run several tools both inside and outside of the virtual machine.

In order to compact the VHD image to only use the xxx GB which it is actually using, we need to follow this procedure:

• Defragment the VHD you are going to compact: You can do this from within the Virtual Machine.
• Precompact the disk: Once the defragmentation is completed, you need to run Virtual Server’s Precompactor. you can do this by shutting down your virtual machine and mount the Precompact.iso on the virtual machine.

On VirtualServer admin screen go to configuration of the machine and under CD/DVD change the Capture to "Known image files" and select Precompact.iso. Click OK

• Compress the disk: From the VS Admin screen menu, click Inspect and find your VHD and click Inspect.

Thats it, sit back and relax :-) after a couple of minutes(or hours) based on the VHD size, you would find that the VHD is compresses to use the same amount of space as seen from withing the virtual machine.

Virtual Server administration website produces a “Page cannot be found error”

I have installed and used Virtual server on both Windows server and Windows XP platforms for a while now. I have come across this issue a couple of times now where in Virtual server installation works fine for a few days and suddenly after installing some security updates, the Virtual Server administration website gives you a “Page cannot be found error”.

I tried to un-install and re-install virtual server software hoping some setting would now be set with the new installation, but that did not work.

I actually rebuilt the operating system and then installed virtual server and bingo it worked. After installing all security patches and services packs, I again got the same “Page cannot be found error”, this basically proved to me that it had to do with some security update, that is causing this issue.

Last week, I read something about a related issue where in a windows security update that restricts access to sites running under default site(port 80) when the files are not under “wwwroot” folder.

I could not create this problem consistently, but definitely solve this problem consistently by doing the following steps:

• Virtual Server by default installs the content to C:\Program files\Microsoft Virtual Server\WebSite\VirtualServer and creates a virtual folder “VirtualServer” under default site.
  
• COPY(not move) the folder: VirtualServer from “C:\Program files\Microsoft Virtual Server\WebSite\VirtualServer” and paste it under “C:\inetpub\wwwroot\”.
  
• Now add a new virtual folder with a different alias(say VS) and point it to this folder which you just copied “C:\inetpub\wwwroot\VirtualServer”
  
• Now try accessing your original Virtual Server administration site or the new one you created and watch it work :-)

One other reason you could be getting the same error would be when you have Virtual server installed and then Virtual PC installed. For some wierd reason these 2 pieces of software donot play well together. In this case, I would suggest uninstalling Virtual PC and see if that helps.

What is ResolveAssemblyReference.cache ?

WHAT: I am pretty sure everybody has come across scenarios, where you think something should work, based on the code changes you have made in visual studio, but they actually dont work...

Well of the many reasons, we found over the last couple of years, recently, i found one more to add to the list.

DETAILS: there is a file created by visual studio called ResolveAssemblyReference.cache, which is created by the newer versions of MSBUILD. The idea of this new feature is to be able to bind to the assembly version while debugging, without having to deploy/GAC the project at all.

If you are not satisfied with the code changes you made, you just have to roll back your code and need not rollback the assembly version which has been deployed/GACed on your system.

WHY did MS implement this?: Kindof like Debugging webpages from your visual studio without having IIS installed on your machine
or
Debug and run a SQL script from your visual studio without having SQL Server on your machine

Just that this functionality created problems for BTSDeploy command line utility, because it used the older version of the DLL that was manually deployed for the package.

SOLUTION: Withing your script, just ensure this cache file is deleted before you dig deeper on why your changes dont take effect.

NOTE: This file can be created only during Compile time not during deployment.

Out of Memory errors while enlisting Biztalk Orchestrations using WMI

Most of the tasks in packaging and deploying Biztalk applications can be done using MSBuild and WMI scripts. All these WMI scripts run under the process 'WMIPrvse.exe'.

Orchestration.enlist is one of most memory consuming task that you could come across while starting applications. When you have many Orchestrations in a single dll, you might come across a 'Out of Memory' error claiming not enough memory is available to complete the transaction, while the server has more than enough memory available.

WMI is used by most management consoles behind the scenes to access certain core functions provided by Microsoft products. Windows Server(2k, 2003) installations come with a smaller memory quota, the default being 128MB for WMI objects.

Problem: When we run trusted scripts, that have to handle large transactions, 128MB is not large enough to complete the transaction and errors out with “Out of Memory” errors.

Solution: There are two ways to solve this:
· Update the appropriate registry(which I still don’t know) setting to a value like 384MB. This can be done HOT and is effective immediately.
· Programmatically increment the ‘MemoryPerHost’ property of the WMI Scripting interface. This would need bouncing the server, before this new quota is effective.

Sample VB Script Code:
'Increase WMI Memory quota from a default of 128MB to 384MB to ensure
'we have enough memory available to complete this process

Dim locator: Set locator = CreateObject("WbemScripting.SWbemLocator")
Dim wmi: Set wmi = locator.ConnectServer("", "root")
Dim quota: Set quota = wmi.Get("__providerhostquotaconfiguration=@")
quota.MemoryPerHost = 384*1024*1024
quota.put_()
Wscript.Echo(quota.MemoryPerHost)


References:
http://blogs.clearscreen.com/tomas/archive/2006/11/13/4234.aspx
http://msdn.microsoft.com/en-us/library/aa394671(VS.85).aspx

How to list files checked out in VSS

How to check files checked out by you across all applications

We have all gotten ourselves in situations where Visual studio automatically checks out files when opening a solution, even though we did not mean to make any changes to those projects.

This happens because your local version was different from the version in VSS, Visual Studio prompts an alert box asking if it should merge/checkout/ignore the first time, based on your selection it does this same selection for ever.

VSS allows for users to search for all files that have been checked out by any given user, so that we don’t have a file locked up accidentally for ever or until somebody needs it.

· Log in to VSS using VSS client(not visual studio)
· From menus on top: Select View >> Search >> Status Search
· Under Status Search: Select “Display files checked out by user” and select your user-id
· Under Search Area: Select “Search in all projects”

Cleaning up Biztalk tracking database

Tracking may tend to grow fast, depending on your how intensive your needs are. Out of the box, BizTalk Server does not offer any tools to "autoclean" the tracking database. However, there's a sample in the BizTalk Server SDK directory exactly targetting the subject. Although it's just a sample, I've never seen this fail and it seems to me that the given SQL script is really high quality coded!!

To cleanup the tracking database:
If you've never done this procedure before, first do this:

1. Open up SQL Server SQL Query Analyzer
2. Connect to the BizTalkDTADb database
3. "File - Open", and go to the BizTalk Server SDK directory and look for the Database Maintenance subdirectory
4. Select the "Purge_DTADB.sql" SQL script file
5. Hit F5
6. You should see a message "Creating stored procedure dtasp_PruneTrackingDatabase", in addition the status bar below should mark the operation as: "Query batch completed".You have just created a new stored procedure called "dtasp_PruneTrackingDatabase". This procedure allows you to prune the tracking database. It takes only one single parameter: @PruneBeforeDate. Executing this SP causes the tracking data to be purged that was older then the given (@PruneBeforeDate) date. So, let's try this.
7. To execute the procedure:
   Select: "File - New - Blank Query Window"
   Type following statements in order to cleanup *all* tracking data before today:
   DECLARE @Today datetimeSET @Today = GETDATE()EXEC dtasp_PruneTrackingDatabase @Today

Depending on how much data is to be purged, after a while the Stored Procedure will notify you with its results. Possibly you may see a message in red, commented as: "Duplicate key was ignored." This is fine and is an expected result.

To verify this:

1. Open HAT
2. Select: "Reporting - Find Message"
3. Try selecting a schema
4. If you've cleaned everything, you should not see any schemas anymore... (Only schemas here should correspond to messages that were tracked, áfter the @PruneBeforeDate date parameter.)

If Tracking database is too huge(> 3 GB), it might be better to truncate the complete content in the tables, rather than trying to clean up one row at a time.

Macros for constructing filenames using Biztalk Send port

Probably every demo of BizTalk 2004 you'll see uses a Send File Port that sends messages to a directory and most of the time these files will have a GUID as a filename, so each message sent will have a different name. The solution is to use "MessageID" macro in the URI property of the Sender Port. So the URI property could look like “c:\temp\out\%MessageID%.xml“. Everything between the percentage signs (including themselves) will be replaced by the GUID which is of course unique.

The files in which the files send handler writes messages can be created dynamically using a predefined set of macros. Before creating a file on file system, the files send handler substitutes all the macros in file name with their individual values. Several different macros can be used in one file name. The file name macros can be used while configuring the file send handler in BizTalk Explorer, or using the Explorer object model.


Below are the list of Macros that could be used for file send ports to generate meaningful names, rather than just a GUID.

• %datetime% : Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508)
• %datetime_bts2000% : UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
• %datetime.tz% : Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
• %DestinationParty% : Name of the destination party. The value comes from message the context property BTS.DestinationParty.
• %DestinationPartyID% : Identifier of the destination party (GUID). The value comes from the message context property BTS.DestinationPartyID.
• %DestinationPartyQualifier% : Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
• %MessageID% : Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
• %SourceFileName% : Name of the file from where the File adapter read the message. The file name includes extension and excludes the file path, for example, foo.xml.
• %SourceParty% : Name of the source party from which the File adapter received the message.
• %SourcePartyID% : Identifier of the source party (GUID). The value comes from the message context property BTS.SourcePartyID.
• %SourcePartyQualifier% : Qualifier of the source party from which the File adapter received the message.
• %time% : UTC time in the format hhmmss.
• %time.tz% : Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).