by Dr. Radut

Editing MSI databases with PowerShell

Published by Laurie Rhodes -

Jason Ginchereau is responsible for the creation of the Windows Deployment Toolkit which is incorporated into WIX.  The toolkit provides extensions for PowerShell to access Windows Installer databases with managed code.  Current releases can be downloaded from: http://wixtoolset.org/releases/

The examples in this page use WixToolkit version 4.0.  The examples are identical with the earlier 3.x versions apart from version 4 now using a different namespace (Toolset.Dtf) for the included objects.

Important Considerations

Almost all MSI alteration occurs with a small handful of functions for inserting and deleting records. 

Three objects are fundamentally important to manipulating a Windows Installer package.

  • Database object– which represents the Windows Installer (MSI) database itself
  • View object – which can be thought of as a window opened onto the database.  The construction of the View determines the dataset that will be returned.
  • Data object – which contains the physical data from the MSI

  We will always open a handle to the database and normally a view (depending on what we want to do).  At the end of our activities we must close the view and dispose of handles to the MSI database otherwise the file will remain locked open until the entire PowerShell session is killed.

Retrieving Property Values

The Properties table of an MSI database holds the most rudimentary data needed for any reporting.  This includes the property values of ProductName, ProductVersion, ProductCode.  Important values for checking include ROOTDRIVE, ALLUSERS and possibly ARPNOREMOVE (depending on how automated the environment is).  The example below demonstrates how to retrieve the value of the ProductVersion Property from a particular database.

[[powershell]]
# Example Queries a local MSI database

# Add Required Type Libraries
Add-Type -Path "C:\Program Files (x86)\WiX Toolset v4.0\bin\WixToolset.Dtf.WindowsInstaller.dll";


# Open an MSI Database
$oDatabase = New-Object WixToolset.Dtf.WindowsInstaller.Database("C:\Temp\Installer.msi");

#Create a Select Query against an individual property
$sSQLQuery = "SELECT * FROM Property WHERE Property= 'ProductVersion'"

#Create and Execute a View object
[WixToolset.Dtf.WindowsInstaller.View]$oView = $oDatabase.OpenView($sSQLQuery)

$oView.Execute()

#Fetch the Result
$oRecord = $oView.Fetch()

$sProductVersion = $oRecord.GetString(2)

#Display Retrieved Field
"Database Version = $($sProductVersion)"

#Close the Database & View
$oView.Close();
$oDatabase.Dispose();
[[/powershell]]

Inserting a New Record

[[powershell]]
# Add Required Type Libraries
Add-Type -Path "C:\Program Files (x86)\WiX Toolset v4.0\bin\WixToolset.Dtf.WindowsInstaller.dll";

# Open an MSI Database
$oDatabase = New-Object WixToolset.Dtf.WindowsInstaller.Database("C:\Temp\Installer.msi", [WixToolset.Dtf.WindowsInstaller.DatabaseOpenMode]::Direct);

[string]$String = "INSERT INTO Property (Property,Value) VALUES ('aaaaa','bbbbb')"

$oDatabase.Execute($String);
$oDatabase.Dispose();
[[/powershell]]

Trial and error has shown a couple of quirks with PowerShell and using “INSERT INTO” sql. 

  •  It only works with Direct DatabaseOpenMode methods – not Transact.
  • SQL must be passed to the oDatabase .Execute and will not work when used with a “View,Execute”

Inserting a New Record (with a data object)

[[powershell]]
# Add Required Type Libraries

Add-Type -Path "C:\Program Files (x86)\WiX Toolset v4.0\bin\WixToolset.Dtf.WindowsInstaller.dll";

# Open an MSI Database
$oDatabase = New-Object WixToolset.Dtf.WindowsInstaller.Database("C:\Temp\Installer.msi", [WixToolset.Dtf.WindowsInstaller.DatabaseOpenMode]::Direct);

[string]$String = "INSERT INTO Property (Property,Value) VALUES (?,?)"

# Create a Record Object for use with the Property Table
[Microsoft.Deployment.WindowsInstaller.TableInfo]$targettable = $oDatabase.Tables['Property'];

$oRecord = New-Object WixToolset.Dtf.WindowsInstaller.Record($targettable.columns.Count);
$oRecord.FormatString = $targettable.Columns.FormatString;
$oRecord.SetString(1,'aaa');
$oRecord.SetString(2,'bbbb');

$oDatabase.Execute($String, $oRecord);

$oDatabase.Dispose();
[[/powershell]]

Modifying an Existing Record

[[powershell]]
# Add Required Type Libraries

Add-Type -Path "C:\Program Files (x86)\WiX Toolset v4.0\bin\WixToolset.Dtf.WindowsInstaller.dll";

# Open an MSI Database
$oDatabase = New-Object WixToolset.Dtf.WindowsInstaller.Database("C:\Temp\Installer.msi", [WixToolset.Dtf.WindowsInstaller.DatabaseOpenMode]::Direct);

#Create a Select Query against an individual property
$sSQLQuery = "SELECT * FROM Property WHERE Property= 'Manufacturer'"

#Create and Execute a View object
[WixToolset.Dtf.WindowsInstaller.View]$oView = $oDatabase.OpenView($sSQLQuery)
$oView.Execute()

#Fetch the Result
$oRecord = $oView.Fetch()
$sProductVersion = $oRecord.GetString(2)

#Display Retrieved Field
"Manufacturer = $($sProductVersion)"

$oRecord.SetString("Value","Fred")
$oView.Modify([WixToolset.Dtf.WindowsInstaller.ViewModifyMode]::Update,$oRecord)

#Close the Database
$oView.Close();
$oDatabase.Dispose();
[[/powershell]]

Creating a Transform

Windows Installer Transforms are “difference” files.  We would make a copy of an original MSI database and edit it to include the changes we needed.  The Transform is generated by comparing the original database to the edited version.  The example below has hardcoded paths for the original MSI, duplicate MSI and created Transform path. 

[[powershell]]
# Example Creates a Transform

# Add Required Type Libraries
Add-Type -Path "C:\Program Files (x86)\WiX Toolset v4.0\bin\WixToolset.Dtf.WindowsInstaller.dll";

$MSIFilePath      = "C:\Temp\Installer.msi"
$MSIDuplicatePath = "C:\Temp\ModifiedInstaller.msi"

# Open an MSI Database in Update Mode
$oAlteredMSI = New-Object WixToolset.Dtf.WindowsInstaller.Database($MSIDuplicatePath,
[WixToolset.Dtf.WindowsInstaller.DatabaseOpenMode]::Direct);

#Original MSI
$oOriginalMSI = New-Object WixToolset.Dtf.WindowsInstaller.Database($MSIFilePath);

$oAlteredMSI.GenerateTransform($oOriginalMSI,"C:\Temp\Transform.mst")

$oAlteredMSI.CreateTransformSummaryInfo( $oOriginalMSI,"C:\Temp\Transform.mst", [WixToolset.Dtf.WindowsInstaller.TransformErrors]::None, [WixToolset.Dtf.WindowsInstaller.TransformValidations]::None )

#Close the Database
$oOriginalMSI.Dispose();
$oAlteredMSI.Dispose();
[[/powershell]]

There is  stunning amount of work that's gone into the Deployment Tools Foundation that deserves real credit for allowing PowerShell full access to the Windows Installer framework.

Tags
Windows Installer
PowerShell