Building custom controls or dataflow components for SQL Server Integration Services (SSIS) can be a bit of a “black art” for business intelligence developers or DBAs, without VB/C# application development experience. The task is not made any easier by the fact that the install procedure for MS BI development tools, and even the tool names(!), seem to keep changing with each new version of SQL Server. The original SSIS dev tool was released with SQL Server 2005 install as part of BIDS, short for Business Intelligence Development Studio. BIDS was then followed by SSDT – SQL Server Data Tools and required Visual Studio install as pre-requisite. Now with 2014, we have what is known as SSDT-BI, SQL Server Data Tools – Business Intelligence. A separate Visual Studio install is still required.
Having recently gone through a SQL Server upgrade, this blog includes a step-by-step proof of concept for making a VB component, originally written for SQL 2008r2, work with SQL Server 2014. For this POC, I selected the Visual Basic version of Microsoft’s “ChangeCase” SSIS dataflow example. Component changes the case of the first letter in a string from lower to Upper within a dataflow. E.g. converting “tuesday” to “Tuesday”. The blog includes steps from installing tools and making necessary code changes, through to ensuring that your component becomes available in the upgraded environment’s SSIS toolbox.
If you get stuck and wish to replicate the process I went through, all download URLs are provided within each relevant section.
Set up SQL Server.
Given my proof of concept runs on Azure, I chose a VM image for SQL Server 2014 called “SQL Server Enterprise Optimized for Data Warehousing Workloads – Windows Server 2012 R2” as my platform.
|Good to know Microsoft offers one month free trial of Azure tools, with enough credit to carry out POCs such as this one online. This way you also get a vanilla environment eliminating noise, such as previous versions of SQL often present in your dev setup. It may also help you to get the steps right first time in order to carry out the upgrade in your production environment.|
Download and install Visual Studio.
Download Visual Studio “Professional 2013 with Update 5” from URL https://www.visualstudio.com/en-us/downloads/download-visual-studio-vs.aspx. Keep in mind that this install is several GBs(!).
For VS installation, I selected all options. My installation took a few hours even though I was using highly specified VM with 8 CPUs and 28GB of memory.
Download and install “SSDT-BI for Visual Studio 2013”.
SSTD-BI enables BI project types within VS. I used the URL https://www.microsoft.com/en-us/download/details.aspx?id=42313 to download it for my version of VS2013. When installing SSDT-BI, select “Perform a new installation of SQL Server 2014” when prompted.
|Good to know Even though Visual Studio install includes option for “Microsoft SQL Server Data Tools”, this does not enable SSIS and other BI project types within VS. Hence SSDT-BI is installed using a separate media.|
Migrate your custom task project into VS2013.
|Good to know Given this process will effectively install a new application (in form of your custom assembly) into your server you will need escalated permissions. Running your Visual Studio as administrator may help you overcome many app installation related issues. This should only be done within the development environment, and you may already have required admin permissions on it.|
Open solution containing your custom task project. Codeplex ULR to .zip file containing the VB ChangeCase example is http://msftisprodsamples.codeplex.com/downloads/get/123740. Project upgrade wizard starts automatically when opening the solution. Wizard only contains a couple steps and enables the project to open within VS2013, so you can start working on it.
At the end of wizard “Migration Report” browser window pops up. This report is indicative of steps required to upgrade the custom component.
In the background, the wizard automatically creates a backup folder of the original project, and a migration log file within the project file folder. These can be removed once the component is successfully upgraded.
Now that the project has successfully loaded in to VS there are number of steps required to make the custom component available within SSDT-BI and for dtexec utility for batch processing.
Update target .net framework from project properties.
Change Target .NET framework from, in my example from 2.0 to 4.5.1.
Changing the target, a message window pops up informing that project will re-open, confirm this.
Register your custom component.
Open the “Compile” tab of the project properties and edit “Post-build Event”. These actions register the assembly in the gac (Global Assembly Cache) and make it available for development and package execution utility.
|Very good to know File path properties of your Post-build Events will depend on your specific environment. Paths depend on version of SQL, installation path, and .net version used. Also keep in mind that VS is 32bit and dtexec a 64bit app, so make your application available to both develop, and test package in batch mode. Custom assembly toolbox selection, i.e. making tool available in your SSIS toolbox, used to be manual task in BIDS. In SSDT-BI this is not possible meaning task must available in the correct folder in order to be discovered by it. Therefore using correct paths is critical.|
In my example I enter the following lines (ones in italics only).
1. Unregistering of any previous versions from GAC
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\gacutil.exe” -u $(TargetName)
2. Make assembly available SSIS package development tool which is 32-bit application.
copy “$(TargetPath)” “C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents\$(TargetFileName)”
3. Assembly is made available to 64-bit application for batch runtime.
copy “$(TargetPath)” “C:\Program Files (x86)\Microsoft SQL Server\120\DTS\PipelineComponents\$(TargetFileName)”
4. Register assembly in GAC in order for it to be available to package execution via dtexec.exe as well as visible in SSDT-BI development tool.
“C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\gacutil.exe” -i “C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents\$(TargetFileName)
Next, ensure that signing information for the assembly is correct. I create a new key for the assembly using “Choose a strong name key file:” drop-menu from “Signing” project property tab, and choose not to pw protect it. Give key a name of your choice.
Once a new key file is created, I remove the existing one.
Fix invalid extension references.
Changing the target .net framework from 2.0 to current 4.5.1 breaks existing library references. We need to make sure that the references from assembly are correct for the target framework. Double-click on ChangeCase.vb to open your code to see the invalid references.
Ensure that all files within the project are visible by clicking “Show All Files” button on top of the Solution Explorer frame. Expand “References” from the Solution Explorer. In my example, you see warning triangles next to some of the references. Delete those references.
Choose correct references by right-clicking on “References” and selecting “Add Reference” to open Reference Manager. Select required references and add them to the project. In my example all required references are available in the Extension section of reference assemblies (Microsoft.SqlServer.DTSPipelineWrap,Microsoft.SqlServer.DTSRuntimeWrap, Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.PipelineHost).
Once references are added back to the project using the correct framework, warning signs next to reference names and squiggly lines in code will disappear.
After checking through every line of the code and ensuring that all references are correct, the last thing left to do is to build the assembly.
Test availability of your component.
After a successful build, check that your new custom component is available and test it works as intended in the development environment. This may require you to restart SSDT-BI, or at minimum use the “Refresh Toolbox” by right clicking inside toolbox and selecting that option.
I hope this POC can help you resolve at least some issues you may encounter in your environment setup or project configuration!