Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Creating an Effortless Custom SSIS Object Installer Using WiX

Matthew Gajdosik

When I first wrote custom objects for SQL Server Integration Services (SSIS), distributing my DLL for use and for installation on servers seemed troublesome. Of course, I had to set up an installer. But after the deprecation of the normal setup/installer project included with Visual Studio, which tool could be used that would be easily shared and maintained, and still meet all of the needs for distributing the custom SSIS objects?

Ultimately, I chose the WiX Toolset, as it is already integrated with the Visual Studio gallery and makes a simple job of product versioning, Global Assembly Cache (GAC) / assembly installation, and install path selection. In this tutorial, I will walk through the construction of a skeleton custom SSIS Task and a corresponding WiX installer, that can locate the correct version of SQL Server, install to both the DTS folders and the assembly, and automatically handle upgrades.

Jump to: Visual Studio Setup | Task Project | WiX Installer | Installation

Visual Studio Setup

This tutorial does assume that you are using a recent version of Visual Studio, and that you are able to install and use the SQL Server Data Tools (SSDT) and WiX Toolset.

SSDT Requirements

If you do not already have SSDT setup, check the appropriate download page on MSDN. It will correspond to the most recent version of Visual Studio (currently 2015), and SQL Server, so you may need to navigate to the Previous Releases section. Most versions have been quite similar since 2012, so you should not run into many issues unless trying to support a much older version, like BIDS.

WiX Toolset Requirements

Since WiX is included in the Visual Studio Gallery, all you need to do is open the Tools > Extensions and Updates menu item from Visual Studio, switch to the Online section, and run a search for it, as below. Of course, you can also directly access any downloads and documentation at the WiX Toolset website.

extensions_wix

Task Project

To begin, let’s run through some basic steps needed to implement a custom Task object. Feel free to skip or adapt any of these steps if you’re already comfortable or have your own existing project.

SSIS and the Visual Studio SSIS editor both need to access the code as a .dll, so start up a new Class Library in C# or VB. Any code snippets used in this example will be written for C#, but the specific .NET language used doesn’t matter.

References

To access the required classes and attributes, you’ll need to locate the SSIS libraries appropriate to the version you’re trying to install. For the purposes of this example, locate the following DLLs:

  • Microsoft.SQLServer.DTSPipelineWrap
  • Microsoft.SQLServer.DTSRuntimeWrap
  • Microsoft.SQLServer.ManagedDTS
  • Microsoft.SQLServer.DTS.Design

If you’re not familiar with the version numbers used for SQL Server, or want to double check your own, Microsoft usually has an up to date list.

Assembly Signing

Due to how SSIS references objects, they should be strongly named. If you are not already familiar with the process of signing your assemblies, you can use the following steps.

  1. Open the project properties for your library project and head to the Signing tab.
    project_properties_signing
  2. Check the “Sign the assembly” box and select “<New…>” in the dropdown.
    sign_assembly_dont_delay_sign
  3. Fill out the details you’d like to use for your key.
    create_strong_name_key
  4. Retrieve the public key token your assembly will use by following these directions, or your own method if you have one. If you follow the linked direction, you will need to adjust the path to cover the versions of the Windows SDK that you have installed. The public key token should be a short hex string, like “9589fa1be527eb6c”.

Task UI Class

Adding a UI class is a good idea, as this will let you make sure that the object has loaded correctly in the editor. Feel free to copy the code below, if needed.

public class SampleTaskUI : IDtsTaskUI {
    public void Delete(IWin32Window parentWindow) {}
    public ContainerControl GetView() {
        return new Form();
    }
    public void Initialize(TaskHost taskHost, IServiceProvider serviceProvider) { }
    public void New(IWin32Window parentWindow) { }
}

Task Class

To set up the skeleton class that will represent the Task object, you can simply start a new class using your own version of the following code:

public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task {}

You may also want to look into writing a Task UI, adding in XML-based saving and loading, and the basic coding tutorial in the Task MSDN documentation. If you’ve had trouble finding a specific solution to your own SSIS problems, of course, feel free to comment below.

Task Icon

One common issue in SSIS is forgetting or misconfiguring the icon that displays in the SSIS Toolbox and in each instance of a custom SSIS object, since this can prevent the objects from loading. All you need to do is add a new Icon item to the project (usually under the General tab), and set it to be an Embedded Resource in the properties. For this example, it’s been saved as WideWorldSampleTask.ico at the root of the WideWorldImporters.SSIS.SampleTask project, so it can be referenced as “WideWorldImporters.SSIS.SampleTask.WideWorldSampleTask.ico”.

If you see an icon like the following when you’ve completed all of your steps and go to use your custom object, it means that the icon hasn’t been included: missing_task_icon

Task Attribute

Now that the Task is defined, you need to provide a DtsTaskAttribute that the SSIS engine uses to identify any Tasks in your library. This attribute provides both basic descriptive information that will be displayed when viewing the Task object in the SSIS Toolbox, and references needed to include the icon and any editor UI classes. Most of the attributes should be self-explanatory, but for this example we’ll be using the simple and commented attribute code listed below.

[DtsTask(
    DisplayName = "Wide World Sample Task",
    Description = "Sample task for demonstration purposes.",
    // Identifies the icon file in the assembly
    IconResource = "WideWorldImporters.SSIS.SampleTask.WideWorldSampleTask.ico",
    // Qualified name for the UI class, which is in this assembly
    // Check the earlier instructions if you’re unsure of your public key token
    // And verify that the version you list here aligns to your project properties
    UITypeName = "WideWorldImporters.SSIS.SampleTask.SampleTaskUI"
        + ",WideWorldImporters.SSIS.SampleTask"
        + ",Version=1.0.0.0"
        + ",Culture=neutral"
        + ",PublicKeyToken=f2ba040aabf4d50b"
        + ",processorArchitecture=MSIL",
    // Indicates if a certain level of SQL Server (i.e. Enterprise) is required
    RequiredProductLevel = DTSProductLevel.None
)]
public class WideWorldSampleTask : Microsoft.SqlServer.Dts.Runtime.Task {}

At this point, you should be able to successfully build this project. However, how do you install your new library?

WiX Installer

The WiX (Windows Installer XML) Toolset is a way to create an XML-driven installer. The level of control this toolset provides is pretty robust, but there are also a number of options offered for easily creating simple installers. In this case, we’ll be creating a new WiX project to our solution, adding a reference to our Task library project, and using one of the pre-configured WiX UIs to accelerate the process.

Destinations

Now, there are two important locations when installing your new SSIS Task .dll. The first is the Microsoft SQL Server/[version]/DTS/Task/ folder, and the second is the GAC (Global Assembly Cache).

The DTS folders are used primarily by the editor interface you see in Visual Studio, and adding the .dll to this folder is required for displaying your new Task in the SSIS Toolbox. If you have not seen them before, you’ll notice folders for the other extensible classes (PipelineComponent, Connections, etc) and for some of the versioning features that you may need later on (UpgradeMappings).

For general use, however, and to make sure that any process executions outside of Visual Studio can find your code (such as when installing to a hosted SQL Server and running the jobs remotely), you will need to make sure the code is visible in the .NET GAC, under /Windows/Microsoft.NET/assembly/. This is a fairly straightforward process when run through an installer, with the only caveat being that very old (BIDS-era) versions of SSIS may need to be added to the older-style /Windows/assembly variant.

To set up your project, simply add a new Setup Project to your solution, under the Windows Installer XML category. If you don’t see this category or option, make sure that you’ve install the WiX Toolset successfully and restarted Visual Studio.

References

Now add a reference to your Task library project, using the general “Add Reference…” dialog, and navigating to the Project tab in the top left.
add_reference_wix          add_project_reference_wix

You may also want to add references to some extra WiX content that can help automate the process. These .dll files can be found in the /bin/ subfolder of the WiX Toolset installation directory, which may be the default under the Browse tab of the “Add References…” dialog. If not, you may want to locate the file path in the File Explorer before continuing. Try adding the suggested extensions below to include the prebuilt UIs and some supplemental utility functions.

  • WixUIExtension
  • WixUtilExtension

Basic WiX

Now, if you’ve generated the default Setup Project, you should have the following XML defined in the Product.wxs file that defines the installer code.

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
  <Product Id="*"
    Name="WideWorldImporters.SSIS.SampleTask Installer"
    Language="1033"
    Version="1.0.0.0"
    Manufacturer=""
    UpgradeCode="e0593b8f-9523-4392-9d83-adb5686296f3" >
    <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />
 
    <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
    <MediaTemplate />
 
    <Feature Id="ProductFeature" Title="WideWorldImporters.SSIS.SampleTask" Level="1">
      <ComponentGroupRef Id="ProductComponents" />
    </Feature>
  </Product>
 
  <Fragment>
    <Directory Id="TARGETDIR" Name="SourceDir">
      <Directory Id="ProgramFilesFolder">
        <Directory Id="INSTALLFOLDER" Name="WideWorldImporters.SSIS.SampleTask" />
      </Directory>
    </Directory>
  </Fragment>
 
  <Fragment>
    <ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER">
      <!-- TODO: Remove the comments around this Component element and the ComponentRef below in order to add resources to this installer. -->
      <!-- <Component Id="ProductComponent"> -->
      <!-- TODO: Insert files, registry keys, and other resources here. -->
      <!-- </Component> -->
    </ComponentGroup>
  </Fragment>
</Wix>

Now what exactly does this code do, and what needs to change to support your new installation process?

  • Product: Defines the overall installer information, primarily the version, any upgrade scheme, and identifying information.
    • MajorUpgrade: Helper element that automatically handles the update process, and stopping installation when you’d be downgrading.
    • Feature: These are the smallest organizational unit for turning features on/off for installation. Because it is included in the main product, its contents will be installed by default.
      • ComponentGroupRef: References a ComponentGroup object for inclusion, typically by pulling the object with a matching ID out of a Fragment.
  • Fragment: These are another sort of “building block” element and contain elements you might want to reference.
    • Directory: These elements define the ultimate path for any installed items or installer actions, and can nest to define a full directory structure. They are typically based on a variable reference (to be determined at install time), or a system folder.
    • ComponentGroup: A group of items to act on. These usually contain one or more Component elements.

Completed WiX

Now compare this to the completed version below:

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
  <!-- Add a variable to your installer to select your version of SQL Server. In this case,
       we use 130 to indicate SQL Server 2014. This should align to the SQL Server DLLs you
       referenced in your project, and therefore the correct DTS subfolder. -->
  <?define sqlVersion=130?>
  <Product Id="*"
           Name="WideWorldImporters.SSIS.SampleTask Installer"
           Language="1033"
           Version="1.0.0.0"
           Manufacturer=""
           UpgradeCode="e0593b8f-9523-4392-9d83-adb5686296f3" >
    <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />

    <!-- Add the special UIRef element to include a UI form defined elsewhere.
         In this case, the WixUI_InstallDir form was defined in the WixUIExtensions.
         This form provides the user the ability to select an installation directory. -->
    <UIRef Id="WixUI_InstallDir" />
    <!-- Map the special ID WIXUI_INSTALLDIR to an ID of your choice.
         This will get used to store the folder the user selects in this form. -->
    <Property Id="WIXUI_INSTALLDIR" Value="SQLSERVERDIR" />

    <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
    <MediaTemplate />
    <Feature Id="ProductFeature" Title="WideWorldImporters.SSIS.SampleTask" Level="1">
      <ComponentGroupRef Id="ProductComponents" />
    </Feature>
  </Product>
  <Fragment>
    <!-- All installations are rooted on a nonexistent "TARGETDIR", for organization. -->
    <Directory Id="TARGETDIR" Name="SourceDir">
      <!-- At this level, you can reference the GAC directly, as it is a special name. -->
      <Directory Id="GAC" Name="GAC" />
      <!-- The DTS folder can be found under the SQL Server folder identified in the UI.
           This re-uses the SQLSERVERDIR Id we mapped to the form earlier. -->
      <Directory Id="SQLSERVERDIR">
        <!-- And finally map the physical child folders to reach the Tasks folder. -->
        <Directory Id="DTSFolder" Name="DTS"> <!-- The Name value selects each child folder. -->
          <Directory Id="TASKFOLDER" Name="Tasks" />
        </Directory>
      </Directory>
    </Directory>
  </Fragment>

  <Fragment>
    <!-- Now add your code files to the ComponentGroup that will be installed. The DLL file
         will be referenced by two components, since it needs to be placed in two locations. -->
    <ComponentGroup Id="ProductComponents">
      <Component Id="Tasks_GAC"
                 Directory="GAC" <!-- This uses the Directory with Id="GAC". -->
                <!-- You can auto-generate a GUID with "*", but this only works with full
                     paths. VS typically includes a tool for GUIDs, and many exist online. -->
                 Guid="93fe15ca-099d-46a1-9070-46723cbc317e">
        <File Id="Tasks_dll_GAC" <!-- This includes the file in the installer process. -->
              <!-- The WiX variables below point to the project output. -->
              Name="$(var.WideWorldImporters.SSIS.SampleTask.TargetFileName)"
              Source="$(var.WideWorldImporters.SSIS.SampleTask.TargetPath)"
              Assembly=".net"
              KeyPath="yes"
              Checksum="yes" />
        <RemoveFile Id="Tasks_dll_GAC" <!-- This removes the file when uninstalling or upgrading. -->
                    On="uninstall"
                    Name="$(var.WideWorldImporters.SSIS.SampleTask.TargetFileName)" />
      </Component>
      <!-- Now that the GAC element is complete, we only need to add the DTS/Task/ file. -->
      <Component Id="Tasks_SQL"
                 <!-- The file is placed under the Task/ directory, with Id="TASKFOLDER". -->
                 Directory="TASKFOLDER"
                 Guid="6b178363-3595-40f7-978d-b299efea0159">
        <!-- ...and the rest follows the GAC example closely, with new and clearly named Ids. -->
        <File Id="Tasks_dll_SQL"
              Name="$(var.WideWorldImporters.SSIS.SampleTask.TargetFileName)"
              Source="$(var.WideWorldImporters.SSIS.SampleTask.TargetPath)"
              KeyPath="yes"
              Checksum="yes" />
        <RemoveFile Id="Tasks_dll_SQL"
                    On="uninstall"
                    Name="$(var.WideWorldImporters.SSIS.SampleTask.TargetFileName)" />
      </Component>
    </ComponentGroup>
  </Fragment>
</Wix>

Installation

Now all you have to do is build your solution and run the installer. If you’ve followed the tutorial code, you should now have an .msi installer in the output of your installer project. Try running it now, and you should see a fairly familiar form:

wix_installer_form_1 wix_installer_form_blank_dir

Since the WiX directories we defined expect the user to root the installation at the top level of their specific version (i.e. /Microsoft SQL Server/130/), you’ll need to fill in this property by hand every time to install correctly.

Directory Search

However, WiX actually provides the tools to automate this selection as well. In short, you can search the registry for existing values, including the root installation directory for SQL Server that should contain the DTS folders. Try adding the following code to your <Product> element, and you’ll be able to retrieve the correct version of SQL Server if it’s been installed, or a default location if not.

<!-- Define a new property and default it to a reasonable location. -->
<Property Id="SQLDIR" Value="C:\Program Files (x86)\Microsoft SQL Server\$(var.sqlVersion)">
  <!-- Then search the registry for either the 32- or 64-bit SQL Server installation directories. -->
  <RegistrySearch Id="SQLDIR_REG_32"
                  Key="SOFTWARE\Microsoft\Microsoft SQL Server\$(var.sqlVersion)"
                  Name="VerSpecificRootDir"
                  Root="HKLM" Type="raw" Win64="no" />
  <RegistrySearch Id="SQLDIR_REG_64"
                  Key="SOFTWARE\Microsoft\Microsoft SQL Server\$(var.sqlVersion)"
                  Name="VerSpecificRootDir"
                  Root="HKLM" Type="raw" Win64="yes" />
</Property>
<!-- Finally, override the directory value used by the InstallDir UI item. -->
<SetDirectory Id="SQLSERVERDIR" Sequence="first" Value="[SQLDIR]" />

Now your installer should default to the correct location, and you can finish installing:

wix_installer_form_auto_dir

Testing

Now to test, all you need to do is check the expected target directories, and try using your new Task.

Depending on how you’ve created your objects, adjust the following paths and verify that your .dll was transferred to each location:

  • C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Tasks
  • C:\Windows\Microsoft.NET\assembly\GAC_MSIL (it will be in subfolders based on your assembly name, .NET version, and assembly version)

If this checks out, open up a new SSIS project and take your Task for a spin. Depending on your version, it may be visible under the “Common” objects folder in the SSIS Toolbox:

ssis_custom_task_basic_test

In our case, we included a blank form, so when you double-click on your task, you should see the following:

ssis_blank_form

Hopefully, this has helped you with your existing project, or motivated you to try custom SSIS programming for yourself; please let us know in the comments below!

_________________________________________________________________________________________

To learn more about Tallan’s Data & Analytics solutions, and how they can transform your organization’s data into business intelligence and action, CLICK HERE.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>