Editing an MSI Database

Abstract

Windows Installer enables efficient installation and configuration of your products and applications. We generally create installers using the Microsoft Build and Deployment project or any external tools like InstallShield etc.

Most of these tools may not have the functionality to edit/update the properties of the installer being generated.

Orca is the tool provided by Microsoft to perform these activities. But this will be a tough task when it comes to modify the files frequently after every build or before every deployment.

This article provides different approaches to perform the same, initially by providing a way how to use Orca tool to edit the MSI database.

In my current project we are using this approach and achieved great time/cost savings to the organization. This approach is incorporated with in the whole organization helping all the build script creators to modify their MSI Installers. This solution is implemented using Orca tool provided by Microsoft, build scripts to build generate MSI’s, VB Script to edit the MSI metadata etc.

Introduction

In an organization, when frequent builds and deployments take place, and at the same time, multiple applications are involved, then its cumbersome for the developer to modify one or more MSI properties/database fields. The tool used to generate the deployment MSI may not have feature to modify the properties during the build. So the approach for it is to modify the properties after the build is complete. This is an additional task for the developer.

This paper attempts to address the above mentioned issues by providing an approach to modify the MSI database values(MSI properties) manually, automatically and within the build script. The approach to modify the properties of the MSI discussed in this paper provides a highly flexible and fast mode of build process involving multiple installers. This approach was tested and implemented. The new process helped save time in building installers and modifying the installer properties.

Need for Modifying the MSI Properties

MSI (Microsoft installer) contains a database of tables consisting of multiple properties. Each used for a specific purpose. Also it contains the list of dependencies/references etc. if for example if an MSI installer has missed one of the reference, the general approach is to rebuild the installer with the appropriate MSI. But the same can be achieved by modifying the MSI database appropriately and get in the required reference.

Another scenario is where the product version needs to be incremented in the MSI. By default this will be 1.0.0. But with every new build of the MSI this needs to be changed so that it can be tracked which version was installed. This feature is not available in the setup and deployment project of Visual Studio.

It can be modified manually using Orca tool, but if the same needs to be modified after every build, it is a time taking task. And if we have more than one MSI then it is still complex.

Need for developing this approach originated due to the requirement of very frequent builds and enhancements as we are following iterative delivery model. Our project contains around 10 MSI’s each being installed on different servers. The deployment on the servers is taken care by a different group, and that group has specific requirements of what the MSI properties should contain including the product version. A build engineer, building 10 MSI’s and modifying the MSI database after every build, is a complex task and also error prone as we also should make sure that all the MSI’s are versioned with the same number per release.

To summarize, below are the major drivers behind this approach:

1. Frequent releases happening due to iterative model.
2. Complex project with many installers.
3. Time consuming process after each build.
4. Manually modifying the properties for many installers is error prone.
5. Setup once by TFS admin, build forever by build engineer.

Modifying MSI properties using Orca

Orca is a Windows Installer package editor provided as part of the Windows Installer SDK and designed to provide full access to the database tables that compose a Windows Installer package. While Orca provides powerful access to all features of the Windows Installer, it is not intended to replace a full-featured package-authoring environment. Orca.exe is a database table editor for creating and editing Windows Installer packages and merge modules. The tool provides a graphical interface for validation, highlighting the particular entries where validation errors or warnings occur.

Orca can be downloaded here for free.

Launch the orca tool and open the MSI which you would like to edit.

Immediately after you open you find that there are lots of tables on the left hand side and the related fields on the right hand side.

Go to the table and the field which you like to edit, and edit the same and save the file.

In one of our scenario, we need to change the product version. It is available in the property table of the MSI. Click on the table on the left side and view the fields on the right. As you can see, the ProductVersion is 1.0.0 by default. Click on the value and change it to the desired value. Save the file.

Details of automating the process

Implementing the process

The project I work on is built on .net Framework 3.0 with C# as the programing language and TFS as configuration management. The MSI build takes place on the build server by taking the code available in TFS. Once the build process is complete, the following will be the output:

1. Compiling of all the code files
2. Build of all the code files and placing the generated assemblies/files in Release folder.
3. Compressing the database items in the Database folder.
4. Generation of Installers using the assemblies/files available in the release folder and placing them in the MSI folder.

Once this build is successful, the developer or the build engineer needs to manually open each MSI in Orca and modify the required properties. Since our project contains 10 installers, and we work in an iterative mode of development, we get to perform this action more than once a week.

I thought of an approach which can automate this process instead of manually opening up each installer and edit the same.

This eliminates the time taken to edit each MSI manually and also eliminate any human errors in the process, as all the installers should have the same ProductVersion.

Below sub sections talk in detail about the steps needed to configure the quick build process and would be applicable to all .NET versions, languages and version control systems.

2. Pre requisites

Before we begin the automated approach, we need to make sure if we have the following pre-requisites.

• MSI installers are already generated.
• Winrunsql.vbs file, available as attachment in this article. This file contains script to update the specified value.

3. Preparing the Batch File

Using the vbscript file we can run a commant which modified the value specified.

In our case the command looks as below:

cscript //nologo "WiRunSql.vbs" "B:\20100903.1\MSI_V\Clinical_2.06.36.msi" "UPDATE Property set Value = '2.06.36' where Property = 'ProductVersion'"

cscript is the command to run the script file.

Note that we are specifying the vbscript file name/path

Next parameter is the name/path to the MSI which we like to modify

Last parameter is the regular update command, updating Value where property is ProductVersion.

Performing this operation every time and making sure the command is correct every time is again a tough task.

So the better approach is to make a batch file with all the commands required.

Copy the above line into a .bat file.

So whenever you need to modify this particular property value, only thing you need to make sure is that the version number is right and the MSI path is right.

4. Integrating into the build process

Again, running the batch file externally after the build, making sure the values in the batch file are right or replace them with a single field replace is again another task in the list which needs to be followed.

So finally I thought of integrating this command into the build script, so that nothing needs to be done by the build engineer, other than building the project. Once the project is build, the MSI’s get generated with the required value modified.

The server build script for the project is available in TFSBuild.proj file.

This file contains code in XML to identify the solutions. The following activities are performed by this file:

• Build each solution and place the assemblies/files in the release folder

• Take the assemblies/files from the release folder, with the help of .vdproj file and package them into MSI’s.

• Place the MSI’s at the right location.

Once the MSI’s are generated, write the code to modify the database property of the MSI.

<Exec Command= "cscript //nologo "$(BuildDirectoryPath)\$(TeamProject)\$(BuildType)\Binaries\Release\WiRunSql.vbs" 
    "$(DropLocation)\$(BuildNumber)\MSI\ClinicalServer_$(VersionNumber).msi" 
    "UPDATE Property set Value = '$(VersionNumber)' where Property = 'ProductVersion'""/>

The XML script above performs the same.

<Exec> specifies to execute and the parameter states it’s a command. It means to run a command specified in the parameter.

Note that we are calling the WiRunSql.vbs. This file is not available by default on the build server. So we need to copy this file to the release folder on the server.

Ways to copy the file to the release folder:

• Add the file to one of the project, the file gets added to the release folder after the build is complete.
• Add the file to the common TFS folders, and copy the file to the release folder in the TFSBuild.proj file.

VersionNumber is the number specified in TFSBuild.rsp file which is part of the TFS build process. This VersioNumber is used by the build to version the assemblies too. In that way, even the assemblies and installers will be in sync with the same version number there by eliminating any more confusion related to the versions.

5. After the build

Once the build is successful we can find the output in the MSI folder with installers with the required property change.

6. What a Build engineer needs to do?

Copy the WiRunSql.vbs  file to the release folder, a onetime task.

Build on the build server from TFS.

Locate the installers and provide to the installation team.

7. Further steps

Modifying the MSI properties in the process defined above is one of the fastest and safest ways.

4. Challenges Faced

1. Copy .vbs file

WiRunSql.vbs file should be placed on the build server. But being a corporate server, we did not have permissions to copy a file to the server. Another place to place it is in the folder where the command executes.

This has been identified as the releases folder and placing the file at that location during every build solved the problem.

It’s actually placing the file in one of the folders in the branch, and copying that file to the release folder using build script.

5. Data Sample

The Table 2 shows the number of installers our application has currently.

Table 3 below compares time taken in best and worst cases for manual and quick build.

After integrating the MSI edit during the build process, there is no additional task needs to be performed. And once this process is tested and verified, then it’s fool proof and there is no chance for a worst case to happen.

6. Advantages of Integrating into Build

Below are the main advantages we are getting the MSI edit functionality with in the build script

• No need to worry about versioning any more. Specify the version in TFSBuild.rsp file, which is usually done to version the assemblies, the same is used by the build to update the version.
• No need to use external tools to modify the MSI database.
• No need of an extra step to perform this activity.
• A onetime modification of the build file can be used across the project.

7. Achievement

The current integration with the build has been implemented to address a long pending pain faced by the deployment engineer, in turn the customer, for the delay in the delivery of the right versioned product.

The innovative approach to version the installer has resulted in huge time saving during the build and deployment process. Additionally it has made the entire process reliable, robust and lesser error prone.

Because of the above advantages, approach presented in the paper has been distributed to different teams and the feedback received has been very encouraging.

8. Acknowledgements

My sincere thanks to my client, who provided an insight into this type of build, over which I have enhanced it. I also thank my company for providing the required platform to share my ideas.

References

1. MSDN.
2. “Professional.Team Foundation Server” by Wrox publications.