I have been making custom SSIS tasks for 3 years now. One of the main issues with them is that the files have to be installed in the correct locations. Datareaders have to be installed in PipelineComponents directory under the SQL server installation and Connection managers need to be installed in the Connections directory under the SQL Server installation. Then to make matters worse both have to be installed in GAC.
I have made windows installers in the past but i find them lacking. In comes Wix Toolset once you get the hang of it wix is a makes making windows installers a lot easier.
setup
- In visual studio add a wix setup project to your solution.
- For each of the projects with in your solution add them as a reference to the wix setup project.
How to find install directory with wix
The first problem we have is finding the location of SQL server on the users pc.
<!– Get install location of SQL Server 2008 –>
<Fragment>
<Property Id=”SQLSERVERINSTALLFOLDER”>
<RegistrySearch Id=’InstallDir’ Type=’directory’ Root=’HKLM’ Key=”SOFTWARE\Microsoft\Microsoft SQL Server\100” Name=”VerSpecificRootDir” />
</Property>
<Condition Message=’Install Folder not found’>SQLSERVERINSTALLFOLDER</Condition>
<ComponentGroup Id=”Fake” />
</Fragment>
As you know the different versions of SQL server have different Data Transformation Services (DTS) locations
- 100 is SQL server 2008
- 110 is SQL server 2012
- 120 is SQL server 2014
- 140 is SQL server 2016
It is annoying but the code for the custom tasks must be different as each one must reference the correct DTS. Depending upon which one you are trying to install you are going to have to read the different location from the users registry.
Directory Structure
<!– map the directory structures –>
<Fragment>
<Directory Id = “TARGETDIR” Name=”SourceDir”>
<Directory Id=”GAC” Name=”GAC” />
<Directory Id=”SQLSERVERINSTALLFOLDER” >
<Directory Id=”DTSDir” Name=”DTS”>
<Directory Id=”PIPELINECOMPONENTS” Name=”PipelineComponents” />
<Directory Id=”CONECTIONS” Name=”Connections” />
</Directory>
</Directory>
</Directory>
We need to have a location for GAC as well as the location from the RegistrySearch which should contain the location of SQL Server install directory SQLSERVERINSTALLFOLDER. It is under the SQLSERVERINSTALLFOLDER that we will find the DTS directory and PipelineComponents and Connections directories where we will need to place our dlls. Remember the dlls must be strong name signed to place them in GAC.
Full code
Conculsion
Using WIX ToolSet to create a setup application to install custom SSIS tasks is not as hard as it would seam. Wix ToolSet lets you make some very professional looking installers and setup applications. I am working on a tutorial series that looks into how to create custom SSIS tasks.