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:
- Cannot change the target .Net Framework version as it always reverts back to original settings in the script task properties
- 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."
- Building the script task would fail and one of the errors would be "The primary reference "Microsoft.SqlServer.ScriptTask, Version=220.127.116.11, 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?