Upgrading SSIS Packages to 2017

Question

The purpose of this question is to document my answer to what seemed to be a strange issue when changing the target SQL Server version of an SSIS package created in some older versions of SSIS prior to 2012 that use script tasks.

An SSIS package originally created in BIDS 2005 or 2008 had no issues when upgraded through the various versions of BIDS/SSDT over the years. Even now when the package was upgraded to SSDT and targeting SQL Server 2016 everything still worked as it always has in addition to being able to take advantage of the latest SSIS features (package parts, project parameters, etc.) and deploying the package through SSDT/Visual Studio to the same target SQL Server. SQL Servers with SSIS installed that were upgraded to 2016 or 2017 seemed to have no issues with packages that were in MSDB or the package store since the jobs continued to run as expected post-upgrade.

So as part of normal upgrade routine, decided to open an SSIS package and upgrade it like before with the previous SSDT changes. In previous SSDT changes, one release worked for a particular version of SSIS. Once they introduced the ability to target some previous versions of SSIS, that feature seemed to work as expected. I figured things would be the same with the latest SSIS and SSDT versions.

However, once changing the target version to SQL Server 2017 within SSDT, there are some things that showed up that SSDT did not catch/fix or could not handle for some reason. Some of the issues I ran into were:

1. Cannot change the target .Net Framework version as it always reverts back to original settings in the script task properties
2. Errors would show up such as "'Dts' is not a member of 'Microsoft.SQLServer'", "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase' is not defined", "Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task.", and "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute' is not defined."
3. Building the script task would fail and one of the errors would be "The primary reference "Microsoft.SqlServer.ScriptTask, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91" could not be resolved because it was built against the ".NETFramework,Version=v4.5" framework. This is a higher version than the currently targeted framework ".NETFramework,Version=v4.0" and the other error I would see was "Namespace or type specified in the Imports 'Microsoft.SqlServer.Dts.Runtime' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases."

The good thing is changing the target back to SQL Server 2016 seemed to work and I could continue editing the packages as before without errors and build would work as expected but the bad news is deploying the 2016 package to an 2017 SSIS server did not get converted like it did during the SQL Server upgrade. So how would I be able to update the packages to target SQL Server 2017 and not have these issues that prevent that?

Answer 1

The main source of the problem is the tags changed in SSIS schema that 2016 and prior handled without issue. I had to view the code of the SSIS package to check for differences and SSIS 2017 seemed to be a bit more strict when it came to the schema within the PropertyGroup node of the Project tag for the script tasks.

Here is what one tag looked like that had errors:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
</PropertyGroup>

The fix that seemed to solve half of my problems were to add the missing entries so that the above now looked like the below code new entries (which are after </ProjectGuid> and before </PropertyGroup>) are:

1. ProductVersion
2. SchemaVersion
3. AppDesignerFolder
4. TargetFrameworkVersion
5. FileAlignment
6. ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch
7. TargetFrameworkProfile

Here is what it looked like after adding the changes:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
  <ProductVersion>8.0.30703</ProductVersion>
  <SchemaVersion>2.0</SchemaVersion>
  <AppDesignerFolder>My Project</AppDesignerFolder>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
  <FileAlignment>512</FileAlignment>
  <ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>true</ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Doing this did not fix the issue with inability to change .Net Framework target so there had to be something else so I looked through the code and found the problem. In the code I found the following after </ProjectExtensions>:

<PropertyGroup>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Since these were now located in the main PropertyGroup of the script task, they are considered redundant so they are no longer needed in the old location. Deleting these entries fixed the other half of the problem. After doing this, not only was I able to change the SQL Server target to 2017, I was also able to change the .Net Framework version and it would not revert. Also, the builds were able to build successfully again.

Fixing the problem this way was easier than to try opening every package and manually creating a new script task, copying the logic from the old one to the new one, making sure the variables and references were the same, and deleting the old task, among other things. That process would take too long especially if there are a lot of packages to go through.

Hopefully you find this useful if you run into this issue.