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.