Implementing a WIX installer that calls the GDR version of VSDBCMD.EXE

Article
10/19/2009

Technorati Tags: Data Dude,DBPro,Database Edition,VSDBCMD,WiX,Setup,MSI

One of the cool things that shipped with the Visual Studio Team System 2008 Database Edition GDR (aka the GDR) is the command line utility VSDBCMD.EXE. By using this utility, you can take the artifacts created by building a Database Project and deploy them to a database instance. If the database doesn’t exist on the instance of SQL Server, it will be created. If the database does exist, it will be altered to look like the source code in your project.

We regularly get questions from developers that are implementing multi-tier applications asking to incorporate database deployment into their WiX based MSI. This blog post describes how you can do that.

An important thing to point out before we get started is that the VSDBCMD.EXE utility has several prerequisites that must be met before the utility will execute successfully. The prerequisites are

Microsoft .NET Framework 3.5 SP1
SQL Compact 3.5 – Note that SQL Compact allows you to redistribute the files that VSDBCMD.EXE requires. So you may optionally include these files in your MSI. The specific files required by VSDBCMD.EXE are described in the MSDN documentation for VSDBCMD.EXE.
SQL 2008 Management Objects

Ensure that these prerequisites are installed on the computers on which you plan to run your MSI.

Development Environment Prerequisites

Visual Studio Team System 2008 Database Edition GDR R2
Windows Installer XML (WiX) Toolset (I recommend you download both the tools and the source code)

Overview

I have written this blog post assuming the reader knows very little about WiX authoring. For the experts out there you can skim the information you are already familiar with. Here’s what we’re going to do in the remainder of this blog post.

Create the Visual Studio Solution
Implement the WiX Installer
- Create the source code files in the project
- Define the directory structure that will be created when the installer runs
- Define the artifacts from the database project that will get installed
- Define the artifacts related to VSDBCMD.EXE that will get installed
- Implement a dialog that will collect the SQL Server instance name and the database name that will be used when the database is installed
- Implement the overall user interface for the installer
- Implement the WiX code that executes VSDBCMD.EXE

Note: On operating systems such as Windows Vista or Windows 7, the installer will need to be run using Administrator privileges (aka run with elevated privileges).

Create the Visual Studio Solution

For the purposes of this blog post we will create a new Database project. If you already have a database project you can simply add it to the solution instead of creating a new one.

Create a new solution containing a database project: File\New\Project\Database Projects\SQL Server 2008\SQL Server 2008 Database Project
1. Set the project’s Name to MyDatabase
2. Set the Solution Name to DatabaseInstaller
3. Click the OK button
4. Add a Table to the database project so that something interesting will actually happen when we run our final MSI.
Add a WiX project to the DatabaseInstaller Solution: File\Add\New Project\Wix\Wix Project
1. Set the project’s Name to InstallMyDatabase
Add References to the InstallMyDatabase project. These are the DLL’s that contain the actual code for executing the setup user interface.
1. Right-click on the InstallMyDatabase project’s References node in Solution Explorer and select Add Reference…
2. Click the Browse tab and specify the DLLs that are named WixUIExtension.dll and WixUtilExtension.dll.
3. Click the Add button.
4. Click the OK button.

Implement the WiX installer

Our installer is pretty simple. It will do the following

Present the user with a License Agreement dialog
Prompt the user for the name of the SQL Server instance he or she wants to install the database on as well as the name of the database. See the screenshot below.
Copy the artifacts created by building our database project to the computer
Copy VSDBCMD.EXE utility and its assemblies to the computer
Execute VSDBCMD.EXE
Exit

Implement the WiX code by doing the following

By default the WiX project has a Product.wxs source file. Let’s add two include files so we can keep things organized
1. Right click on project InstallMyDatabase in Solution Explorer, Select Add\New Item…\WiX Include File
2. Set the Name for the file to DatabaseArtifacts.wxi. This file will contain the setup authoring for the artifacts created when we build our database project.
3. Add a second file and Name it VsdbcmdArtifacts.wxi. This file will contain the setup authoring for the VSDBCMD.EXE utility and the files it requires.
Define the folder structure that we need to create by adding the following code to the Product.wxs file immediately after the Media element. This code will result in the path “Program Files\MyDatabase\Extensions\SqlServer\2008\DbSchemas” being created on the user’s system.
```
     
    <Directory Id="TARGETDIR" Name="SourceDir">
      <Directory Id="ProgramFilesFolder">
        <Directory Id="INSTALLLOCATION" Name="MyDatabase">
          <Directory Id="Extensions" Name="Extensions">
            <Directory Id="SqlServer" Name="SqlServer">
              <Directory Id="__2008" Name="2008">
                <Directory Id="DbSchemas" Name="DbSchemas">
                </Directory>
              </Directory>
            </Directory>
          </Directory>
        </Directory>
      </Directory>
    </Directory>
```
A couple interesting things to note about the values in the Id attributes for the <Directory> elements; 1) As you will see, WiX turns these values into properties that we can use in our authoring and 2) properties that are UPPERCASED can be overridden from the command line when the MSI is run. I’ll demonstrate this in the section that covers running the MSI we’re building.

Now include the two wxi files we created earlier

     <?include $(var.SolutionDir)InstallMyDatabase\DatabaseArtifacts.wxi ?>
    <?include $(var.SolutionDir)InstallMyDatabase\VsdbcmdArtifacts.wxi?>

Open the file DatabaseArtifacts.wxi and replace <!-- TODO: Put your code here. –> with the following code

   <DirectoryRef Id="INSTALLLOCATION">
    <Component Id="DatabaseSqlcmd" Guid="PUT-GUID-HERE">
      <File Id="Database.sqlcmdvars" Source="$(var.SolutionDir)MyDatabase\sql\debug\Database.sqlcmdvars" KeyPath="yes"/>
    </Component>
    <Component Id="DatabaseSqldeployment" Guid="PUT-GUID-HERE">
      <File Id="Database.sqldeployment" Source="$(var.SolutionDir)MyDatabase\sql\debug\Database.sqldeployment" KeyPath="yes"/>
    </Component>
    <Component Id="DatabaseSqlsettings" Guid="PUT-GUID-HERE">
      <File Id="Database.sqlsettings" Source="$(var.SolutionDir)MyDatabase\sql\debug\Database.sqlsettings" KeyPath="yes"/>
    </Component>
    <Component Id="DATABASEDBSCHEMA" Guid="PUT-GUID-HERE">
      <File Id="Database1.dbschema" Source="$(var.SolutionDir)MyDatabase\sql\debug\MyDatabase.dbschema" KeyPath="yes"/>
    </Component>
    <Component Id="DatabaseDeploymanifest" Guid="PUT-GUID-HERE">
      <File Id="Database1.deploymanifest" Source="$(var.SolutionDir)MyDatabase\sql\debug\MyDatabase.deploymanifest" KeyPath="yes"/>
    </Component>
    <Component Id="MicrosoftSqlTypesDbschema" Guid="PUT-GUID-HERE">
      <File Id="Microsoft.SqlTypes.dbschema" Source="$(var.SolutionDir)MyDatabase\sql\debug\Microsoft.SqlTypes.dbschema" KeyPath="yes"/>
    </Component>
    <Component Id="PostDeploymentScript" Guid="PUT-GUID-HERE">
      <File Id="Script.PostDeployment.sql" Source="$(var.SolutionDir)MyDatabase\sql\debug\Script.PostDeployment.sql" KeyPath="yes"/>
    </Component>
    <Component Id="PreDeploymentScript" Guid="PUT-GUID-HERE">
      <File Id="Script.PreDeployment.sql" Source="$(var.SolutionDir)MyDatabase\sql\debug\Script.PreDeployment.sql" KeyPath="yes"/>
    </Component>
  </DirectoryRef>

Complete the code by replacing each instance of PUT-GUID-HERE with your own unique GUIDs. You can launch a GUID generation tool from the Visual Studio Tools menu: Tools\Create GUID. Choose the Registry Format and delete the open and close curly braces “{ }” after you paste the GUIDs into the code.

Those of you familiar with WiX syntax will understand all the details of this code. For you rookies that don’t feel like reading the WiX documentation, here’s what’s going on:

<DirectoryRef>	This element “references” the <Directory> element with the matching Id. It tells WiX that the components contained inside will get installed to the folder INSTALLLOCATION.
<Component>	A component is a group of things that need to be installed as one atomic unit. All or nothing, so to speak. Each <Component> must have it’s own Guid. Guids are how the Windows Installer keeps track of components. It is strongly recommended that each component contain a single <File>. The reason for this recommendation has to do with servicing. If you ever need to create a patch you will reference the component(s) that needs to be serviced.
$(var.SolutionDir)	A useful variable that is implemented by the WiX compiler’s pre-processor. Saves you from worrying about relative/changing paths.
KeyPath	This attribute tells the Windows Installer (aka msiexec) to use this file to determine whether or not the component is installed on the system.

Open the file VsdbcmdArtifacts.wxi and replace <!-- TODO: Put your code here. –> with the following code

   <!--Make sure we have the correct path to the Data Dude redistributables regardless
  of the OS architecture.-->
  <?if $(var.Platform)=x64 ?>
    <?define SrcArchPath = "C:\Program Files (x86)\Microsoft Visual Studio 9.0\VSTSDB\Deploy\"?>
  <?else?>
    <?define SrcArchPath = "C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy\"?>
  <?endif?>

  <DirectoryRef Id="INSTALLLOCATION">
    <Component Id="MicrosoftDataSchema" Guid="PUT-GUID-HERE">
      <File Id="Microsoft.Data.Schema.dll" Source="$(var.SrcArchPath)Microsoft.Data.Schema.dll" KeyPath="yes" Checksum="yes"/>
    </Component>
    <Component Id="MicrosoftDataSchemaScriptDom" Guid="PUT-GUID-HERE">
      <File Id="Microsoft.Data.Schema.ScriptDom.dll" Source="$(var.SrcArchPath)Microsoft.Data.Schema.ScriptDom.dll" KeyPath="yes" Checksum="yes"/>
    </Component>
    <Component Id="MicrosoftDataSchemaScriptDomSql" Guid="PUT-GUID-HERE">
      <File Id="Microsoft.Data.Schema.ScriptDom.Sql.dll" Source="$(var.SrcArchPath)Microsoft.Data.Schema.ScriptDom.Sql.dll" KeyPath="yes" Checksum="yes"/>
    </Component>
    <Component Id="MicrosoftDataSchemaSql" Guid="PUT-GUID-HERE">
      <File Id="Microsoft.Data.Schema.Sql.dll" Source="$(var.SrcArchPath)Microsoft.Data.Schema.Sql.dll" KeyPath="yes" Checksum="yes"/>
    </Component>
    <Component Id="Vsdbcmd" Guid="PUT-GUID-HERE">
      <File Id="vsdbcmd.exe" Source="$(var.SrcArchPath)vsdbcmd.exe" KeyPath="yes" Checksum="yes"/>
    </Component>
  </DirectoryRef>

  <DirectoryRef Id="SqlServer">
    <Component Id="DatabaseSchemaProvidersExtensionsXml" Guid="PUT-GUID-HERE">
      <File Id="DatabaseSchemaProviders.Extensions.Xml" Source="$(var.SrcArchPath)Extensions\SqlServer\DatabaseSchemaProviders.Extensions.xml" KeyPath="yes"/>
    </Component>
  </DirectoryRef>

  <DirectoryRef Id="DbSchemas">
    <Component Id="MicrosoftSqlTypes" Guid="PUT-GUID-HERE">
      <File Id="MicrosoftSqlTypesDbschema" Source="$(var.SrcArchPath)Extensions\SqlServer\2008\DBSchemas\Microsoft.SqlTypes.dbschema" KeyPath="yes"/>
    </Component>
  </DirectoryRef>

   <!--Install the Visual Studio 9.0 registry key if it doesn't exist. -->
  <Feature Id="RegistryPreReqs" Title="Registry Prerequisites" Level="1">
    <ComponentRef Id="RegistryEntries"/>
    <Condition Level="0">NOT VISUALSTUDIOREGISTRY</Condition>
  </Feature>

  <!--VSDBCMD requires this REGKEY to be installed. Note that this will NOT be true for the Visual Studio 2010 version-->
  <!--HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0-->

  <!-- Define the search for the Visual Studio 9.0 registry key -->
  <Property Id="VISUALSTUDIOREGISTRY">
    <RegistrySearch Id="VisualStudio90Registry"
                    Root="HKCU"
                    Key="Software\Microsoft\VisualStudio\9.0"
                    Name="(Default)"
                    Type="raw" />
  </Property>

  <!-- Define the Visual Studio 9.0 Registry entry-->
  <DirectoryRef Id="TARGETDIR">
    <Component Id="RegistryEntries" Guid="PUT-GUID-HERE">
      <RegistryKey Root="HKCU"
                   Key="Software\Microsoft\VisualStudio\9.0"
                   Action="create"
                     >
        <RegistryValue Type="string" Name="(Default)" Value="Default Value" KeyPath="no"/>
      </RegistryKey>
    </Component>
  </DirectoryRef>

Just like you did previously, replace each instance of PUT-GUID-HERE with your own GUIDs. There are a few new things to point out in this code

<?if…	While I was implementing this code I was working on the project on a couple different computers. One was running a 32-bit OS and the other was running a 64-bit OS. Well vsdbcmd.exe and it’s dependencies are installed in under “Program Files“ or “Program Files (x86) depending on the architecture of the OS. So, I took advantage of the WiX’s pre-processor’s conditional logic and predefined variable $(var.Platform) to define my own variable representing the path to the files I needed.
Checksum	This attribute tells the Windows Installer that the file has a checksum that may be used to validate that the file hasn’t been tampered with. You should always set it to "yes" for executables (dll’s and exe’s).
<Feature>	In the Windows Installer world a Product is composed of one or more Features. I am using this feature to define a component that I want installed if it does not already exist on the target system. The<ComponentRef> element defines the component I want installed. Specifically, the RegistryEntries component. The feature’s <Condition> element indicates that I want to feature installed the expression (NOT VISUALSTUDIOREGISTRY) evaluates to true.
VISUALSTUDIOREGISTRY	This is a property that represents the results of the <RegistrySearch>. The attributes of the element define the particular registry key to search for.
RegistryEntries	This <Component> defines the registry key that will be created assuming the <RegistrySearch> came back empty.

Now that we have all of our components defined, we need to tell WiX to actually install them. Open the Product.wxs file again and add the following code immediately after the <?include?> elements

     <!-- Define which files to install -->
    <Feature Id="ProductFeature" Title="InstallMyDatabase" Level="1">
      <ComponentRef Id="DatabaseSqlcmd"/>
      <ComponentRef Id="DatabaseSqldeployment"/>
      <ComponentRef Id="DatabaseSqlsettings"/>
      <ComponentRef Id="DATABASEDBSCHEMA"/>
      <ComponentRef Id="DatabaseDeploymanifest"/>
      <ComponentRef Id="MicrosoftSqlTypesDbschema"/>
      <ComponentRef Id="PostDeploymentScript"/>
      <ComponentRef Id="PreDeploymentScript"/>
      <ComponentRef Id="MicrosoftDataSchema"/>
      <ComponentRef Id="MicrosoftDataSchemaScriptDom"/>
      <ComponentRef Id="MicrosoftDataSchemaScriptDomSql"/>
      <ComponentRef Id="MicrosoftDataSchemaSql"/>
      <ComponentRef Id="Vsdbcmd"/>
      <ComponentRef Id="DatabaseSchemaProvidersExtensionsXml"/>
      <ComponentRef Id="MicrosoftSqlTypes"/>
      <ComponentRef Id="RegistryEntries"/>
    </Feature>

Similar to the registry stuff that I described previously, this code defines a <Feature> which contains <ComponentRef> elements, one for each component that we want to install on the user’s system.

At this point you can build your WiX project and run the MSI that is created. Go ahead and build your solution. Open a command shell with elevated privileges (aka Administrator) and change directories to the location of the new MSI. The MSI is placed in <Project Folder>\bin\debug. Execute the MSI using the command line: msiexec /i InstallMyDatabase.msi /l*vx log.txt

The installation will install each of the components onto your system. An entry will also be added to the Add Remove Programs (ARP) list. To uninstall run the command line: msiexec /x InstallMyDatabase.msi /l*vx log.txt

Our MSI currently doesn’t have an user interface other than a progress bar. What we really want is the typical dialog sequence that shows the user an End User License Agreement (EULA), collects information needed for the install, shows progress then finishes. WiX provides everything we need with the exception of the dialog to capture information about the SQL server and the database name we need to use when we deploy our database. Insert the following code in the Product.wxs file immediately after the <Feature> element we defined previously.

     <!--Define the dialog to get the Server and Database name information from the user-->
    <UI>
      <Dialog Id="DatabaseInformationDialog" Width="370" Height="270" Title="[ProductName] [Setup]" NoMinimize="yes">
        <Control Id="ServerNameLabel" Type="Text" X="45" Y="73" Width="100" Height="15" TabSkip="no" Text="SQL Instance Name:" />
        <Control Id="ServerNameEdit" Type="Edit" X="45" Y="85" Width="220" Height="18" Property="SERVERNAME" Text="{80}" />
        <Control Id="DatabaseNameLabel" Type="Text" X="45" Y="110" Width="100" Height="15" TabSkip="no" Text="Database Name:" />
        <Control Id="DatabaseNameEdit" Type="Edit" X="45" Y="122" Width="220" Height="18" Property="DATABASENAME" Text="{80}" />
        <Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="&amp;Back"/>
        <Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="&amp;Next"/>
        <Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="Cancel">
          <Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
        </Control>
        <Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="WixUI_Bmp_Banner" />
        <Control Id="Description" Type="Text" X="25" Y="23" Width="280" Height="15" Transparent="yes" NoPrefix="yes">
          <Text>”Please enter a SQL instance and database name.”</Text>
        </Control>
        <Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
        <Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes">
          <Text>{\WixUI_Font_Title}”SQL instance and database information.”</Text>
        </Control>
        <Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
      </Dialog>
    </UI>

This code defines a dialog that contains two labels and associated text input controls. When the user enters text into the ServerNameEdit and DatabaseNameEdit text controls it is persisted in the properties SERVERNAME and DATABASENAME, respectively.

Now we will define the overall user interface for our installer. I did this by copying the source code from the WixUI_Mondo dialog definition, renaming the Id and customizing it to include my DatabaseInformationDialog dialog. Insert the following code immediately following the <UI> element we just added.

     <!--Define the overall user interface - this is based on the WixUI_Mondo dialog set-->
    <UI Id="MyWixUI">
      <TextStyle Id="WixUI_Font_Normal" FaceName="Tahoma" Size="8" />
      <TextStyle Id="WixUI_Font_Bigger" FaceName="Tahoma" Size="12" />
      <TextStyle Id="WixUI_Font_Title" FaceName="Tahoma" Size="9" Bold="yes" />

      <Property Id="DefaultUIFont" Value="WixUI_Font_Normal" />
      <Property Id="WixUI_Mode" Value="Mondo" />

      <DialogRef Id="ErrorDlg" />
      <DialogRef Id="FatalError" />
      <DialogRef Id="FilesInUse" />
      <DialogRef Id="MsiRMFilesInUse" />
      <DialogRef Id="PrepareDlg" />
      <DialogRef Id="ProgressDlg" />
      <DialogRef Id="ResumeDlg" />
      <DialogRef Id="UserExit" />

      <Publish Dialog="ExitDialog" Control="Finish" Event="EndDialog" Value="Return" Order="999">1</Publish>

      <Publish Dialog="WelcomeDlg" Control="Next" Event="NewDialog" Value="LicenseAgreementDlg">1</Publish>

      <Publish Dialog="LicenseAgreementDlg" Control="Back" Event="NewDialog" Value="WelcomeDlg">1</Publish>
      <Publish Dialog="LicenseAgreementDlg" Control="Next" Event="NewDialog" Value="DatabaseInformationDialog" Order="2">LicenseAccepted = "1"</Publish>

      <Publish Dialog="DatabaseInformationDialog" Control="Next" Event="NewDialog" Value="SetupTypeDlg">1</Publish>
      <Publish Dialog="DatabaseInformationDialog" Control="Back" Event="NewDialog" Value="LicenseAgreementDlg">1</Publish>

      <Publish Dialog="SetupTypeDlg" Control="Back" Event="NewDialog" Value="DatabaseInformationDialog">1</Publish>
      <Publish Dialog="SetupTypeDlg" Control="Back" Event="NewDialog" Value="LicenseAgreementDlg">1</Publish>
      <Publish Dialog="SetupTypeDlg" Control="TypicalButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
      <Publish Dialog="SetupTypeDlg" Control="CustomButton" Event="NewDialog" Value="CustomizeDlg">1</Publish>
      <Publish Dialog="SetupTypeDlg" Control="CompleteButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>

      <Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="1">WixUI_InstallMode = "Change"</Publish>
      <Publish Dialog="CustomizeDlg" Control="Back" Event="NewDialog" Value="SetupTypeDlg" Order="2">WixUI_InstallMode = "InstallCustom"</Publish>
      <Publish Dialog="CustomizeDlg" Control="Next" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>

      <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="CustomizeDlg" Order="1">WixUI_InstallMode = "InstallCustom"</Publish>
      <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="SetupTypeDlg" Order="2">WixUI_InstallMode = "InstallTypical" OR WixUI_InstallMode = "InstallComplete"</Publish>
      <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="CustomizeDlg" Order="3">WixUI_InstallMode = "Change"</Publish>
      <Publish Dialog="VerifyReadyDlg" Control="Back" Event="NewDialog" Value="MaintenanceTypeDlg" Order="4">WixUI_InstallMode = "Repair" OR WixUI_InstallMode = "Remove"</Publish>

      <Publish Dialog="MaintenanceWelcomeDlg" Control="Next" Event="NewDialog" Value="MaintenanceTypeDlg">1</Publish>

      <Publish Dialog="MaintenanceTypeDlg" Control="ChangeButton" Event="NewDialog" Value="CustomizeDlg">1</Publish>
      <Publish Dialog="MaintenanceTypeDlg" Control="RepairButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
      <Publish Dialog="MaintenanceTypeDlg" Control="RemoveButton" Event="NewDialog" Value="VerifyReadyDlg">1</Publish>
      <Publish Dialog="MaintenanceTypeDlg" Control="Back" Event="NewDialog" Value="MaintenanceWelcomeDlg">1</Publish>
    </UI>


    <UIRef Id="WixUI_Common" />

I injected my new dialog in the overall sequence changing the LicenseAgreementDlg dialog’s Next button event to open my DatabaseInformationDialog and the Next button in my dialog subsequently launches the SetupTypeDlg. Similarly for the Back button events.

Once again you can build and run the MSI at this point. You will see the new dialog we have defined. There is one significant piece still missing, however. That is actually running VSDBCMD.EXE to install our database. To implement this add the following code.
```
     
    <CustomAction Id="LaunchVsdbcmdCommandLine"
                  Property="LaunchVsdbcmd"
                  Value="&quot;[#vsdbcmd.exe]&quot; /a:Deploy /cs:&quot;Server=[SERVERNAME];Integrated Security=true;&quot; /dsp:Sql /dd+ /model:&quot;[INSTALLLOCATION]Database1.dbschema&quot; /p:TargetDatabase=&quot;[DATABASENAME]&quot; /DeploymentScriptFile:&quot;[INSTALLLOCATION][DATABASENAME].sql&quot;"
                  Execute="immediate"/>
    
    <CustomAction Id="LaunchVsdbcmd" BinaryKey="WixCA" DllEntry="CAQuietExec" Execute="deferred" Return="check" Impersonate="yes"/>

    
    <InstallExecuteSequence>
      <Custom Action="LaunchVsdbcmdCommandLine" Before="InstallFiles"/>
      <Custom Action="LaunchVsdbcmd" After="InstallFiles"/>
    </InstallExecuteSequence>
```
I found this code to be the most difficult part of the whole project to get right. The first custom action’s (LaunchVsdbcmdCommandLine) purpose is to create the command line string that the second custom action (LaunchVsdbcmd) will actually execute. LaunchVsdbcmdCommandLine declares the property LaunchVsdbcmd and sets its Value. The identifiers inside [] are properties that will get bound to their actual values when the custom action is executed. The “#” inside the property references gets expanded to the folder where the files are being installed to (aka INSTALLLOCATION). The second custom action LaunchVsdbcmd uses the property with the name that matches the custom action’s Id as the command line when it invokes the entry point CAQuietExec. The custom action LaunchVsdbcmd knows to do this because the Execute attribute is set to deferred. The <InstallExecuteSequence> element is pretty straightforward. It defines when the Windows Installer will actually execute each custom action. Windows Installer executes a series of “actions.” InstallFiles is one of those actions. I chose this action because I know the user has entered the values for SERVERNAME and DATABASENAME. After this action is complete I know all the files I need are installed on the user’s system.
So that’s it. We now have an MSI that will deploy our database when it is run. Use the msiexec command line described earlier to execute the installer. Analyze the log file if the MSI fails for some reason. Any error messages returned by VSDBCMD.EXE will be captured in the log file.

What’s Missing?

There are numerous additional things that you will probably want to do in your MSI. For example, display your own EULA, make the project localizable or check that all of the VSDBCMD.EXE prerequisites exist on the system before attempting to run the utility. I leave the implementation of these additional features to you. If you would like to share your enhancements, implementation(s) or give me feedback on this blog post please attach a comment or send me a note: duke dot kamstra at microsoft dot com.

Comments

Anonymous
December 05, 2009
Hi Duke, There seems to be a small issue with the code in p.10. The WixCA binary key does not resolve at the build time for CustomAction Id="LaunchVsdbcmd". Did you mean BinaryKey="Vsdbcmd" there by any chance? Regards, Vlad
Anonymous
December 05, 2009
Sorry, my bad. I didn't pay attention to the required references for the WiX project. Great post! Vlad
Anonymous
April 13, 2010
The comment has been removed
Anonymous
December 21, 2010
Are there changes needed for VS2010 VSDBCMD? (except the obvious 9.0 => 10.0)
Anonymous
February 09, 2011
The comment has been removed
Anonymous
February 10, 2011
Hello all, If anyone is interested I have taken Duke's code, adapted it for VS2010 and blogged about it at: sqlblog.com/.../building-a-database-installer-with-wix-datadude-and-visual-studio-2010.aspx Jamie
Anonymous
March 01, 2011
One more thing that we find is good to do is to have a "test connection" button on one of the forms so that once you've entered your username / password (or trusted connection) you can't continue till you check that you can connect to the database.
Anonymous
May 15, 2012
The comment has been removed

Partager via