SSIS script component failing after upgrading from 2016 to 2019

Question

We are in the process of updating our SSIS packages from SQL Server 2016 to SQL Server 2019. Many of these packages were developed years ago but others have been created using BIML over the last couple of years. Upon updating the TargetServerVersion from SQL Server 2016 to SQL Server 2019, many of the packages are throwing errors on the Script Components. The errors reference missing directives or assemblies. There are also warnings that refer to the .NET framework being incorrect. This seems to be the primary issue and we see that with the failures the Target framework is stuck on .NET Framework 4.

The error messages are as follows:
The type or namespace name 'ScriptBuffer' could not be found (are you missing a using directive or an assembly reference?).

The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

The warning texts are as follows:
The primary reference "Microsoft.SqlServer.TxScript, Version=15.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91" could not be resolved because it has an indirect dependency on the assembly "Microsoft.SqlServer.ManagedDTS, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" which was built against the ".NETFramework,Version=v4.6.2" framework. This is a higher version than the currently targeted framework ".NETFramework, Version=v4.0".

There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "Microsoft.SqlServer.DTSRuntimeWrap, Version=15.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.

We have several developers working on this issue as it impacts over 500 packages. Here is a sample of what we have tried so far:
• Downgrade project TargetServerVersion to 2016 and back to 2019
• Downgrade project TargetServerVersion to 2016 then to 2017 and then to 2019
• Build/rebuild script
• Attempted upgrade in both VS 2017 and VS 2019
• Downgrade project TargetServerVersion to 2012 then to 2016 to 2017 to 2019
• Applied latest updates to VS 2019; removed SSDT and re-added; added in extensions including .NET packages (targeting packs)
• Repaired VSTA
• Attempted upgrade to SQL 2017 in VS 2015

The most pertinent system specifics are below:

Microsoft Visual Studio Community 2019
Version 16.10.3
VisualStudio.16.Release/16.10.3+31424.327
Microsoft .NET Framework
Version 4.7.03190

Installed Version: Community

Microsoft Visual Studio Tools for Applications 2019 00435-60000-00000-AA203
Microsoft Visual Studio Tools for Applications 2019

SQL Server Analysis Services 15.0.19623.0
Microsoft SQL Server Analysis Services Designer
Version 15.0.19623.0

SQL Server Data Tools 16.0.62106.24090
Microsoft SQL Server Data Tools

SQL Server Integration Services 15.0.2000.167
Microsoft SQL Server Integration Services Designer
Version 15.0.2000.167

SQL Server Reporting Services 15.0.19528.0
Microsoft SQL Server Reporting Services Designers
Version 15.0.19528.0

Along with the code in one of the tasks - these are relatively simple scripts:

/* Microsoft SQL Server Integration Services Script Component  
*  Write scripts using Microsoft Visual C# 2008.  
*  ScriptMain is the entry point class of the script.*/  
  
using System;  
using System.Data;  
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;  
using Microsoft.SqlServer.Dts.Runtime.Wrapper;  
  
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]  
public class ScriptMain : UserComponent  
{  
    private int ETLRowNumber;  
  
    public override void PreExecute()  
    {  
        base.PreExecute();  
        ETLRowNumber = 1;  
    }  
  
    public override void Input0_ProcessInputRow(Input0Buffer Row)  
    {  
        Row.ETLBatchID = Variables.ETLBatchID;  
        Row.ETLBatchPackageID = Variables.ETLBatchPackageID;  
        Row.ETLRowNumber = ETLRowNumber;  
        ETLRowNumber++;  
    }  
  
}  

We have found references to similar issues and have attempted all of the various suggestions. Ideally, we don’t have to add a new script component to each package and port the code over. This would be a manual effort for the 500+ packages that suffer this same issue.

Here are the links to those similar issues:
[https://learn.microsoft.com/en-us/answers/questions/261916/the-binary-code-for-the-script-is-not-found-vs2019.html][1]
[https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5f0bef4a-c571-4e97-a191-377ea4e3e7f8/ssis-script-component-not-updating-net-framework-after-2012-to-2017-upgrade?forum=sqlintegrationservices][2]

Any help you can provide is much appreciated. Thanks in advance!

I've also attached screenshots of the script properties and errors/warnings.
[115484-package-upgrade-errors.png][3]

Answer 1

Hi @ZoeHui-MSFT ,

We are using the latest SSIS projects. Our current IS projects designer is build 15.0.2000.167.

Thanks so much for your reply and helpful links. The 2nd link you provided was the most relevant for our purposes. Another developer on our team had run across a similar post and spent some time working through the steps over the weekend. Our ultimate fix is as follows -

Please note: our packages are currently set to a TargetServerVersion of SQL Server 2016.

--change TargetServerVersion to SQL Server 2019
--open the .dtsx file for each package in Notepad++ or other text editor
--remove the following for each dtsx file (there were 2 occurences - 1 for each script component in our packages):
<PropertyGroup><TargetFrameworkVersion>v4.0</TargetFrameworkVersion></PropertyGroup>
--save the .dtsx file
--go back into Visual Studio and rebuild the project file

Upon opening the packages the errors messages are now gone and if you open the script in VSTA the .NET framework is correctly to 4.7. 4.7 agrees with the SQL Server 2019 target. We have tested that the packages run successfully against a 2019 instance and that has worked great.

Due to the large number of packages that this affects we're now attempting to come up with a PowerShell script to manipulate the XML and remove that PropertyGroup value. It's more likely than not that we'll need to work through this a project at a time, rebuild those projects, and deploy for testing. Based on manual modifications of several projects the results are quiet positive.

Thanks again for your reply. I'll also look to post the PS script once we have that in hopes that it might help others.
-Ken

Answer 2

This is a very well known issue. Script tasks require recompilation after migrating to 2019 as they hold onto old .net references. Some people hack the xml of the packages to correct this. Our approach was to use the BiXpress recompilation option for packages in a project.

Same thing happens the first time you open a package - it automatically recompiles the script tasks. Then when you close them and re-open they are fine. However that can take a really long time - up to several minutes per package!

Bottom line is there's no easy answer. Either hack the XML (and assume risk) or find a way to recompile all your packages - either via say ps script or opening one at a time or utility like BiXpress.

Answer 3

Answer 4

Hi @Anonymous ,

Are you using the newest SQL Server Integration Services Projects?

https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects

Also you may refer this blog which provided some methods you may take a carefully reference.

https://www.koskila.net/how-to-fix-the-type-or-namespace-name-services-does-not-exist-in-the-namespace-microsoft-aspnetcore-components-are-you-missing-an-assembly-reference/

https://dba.stackexchange.com/questions/216263/upgrading-ssis-packages-to-2017

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October