VS 2019 : Custom SSIS connection

Question

Hi all,

I'm facing a strange behaviour with VS 2019 and SSIS 2019.

I've developped a set of custom tasks, components and connections for SSIS used for month with VS 2017 et SSIS 2016.
All in a single assembly.

Once upgraded to VS 2019 and SQL Server 2019 everthing works fine, but only if the target version of SQL server is 2016.

When I set the target to 2019, Tasks and components works well, but the connection give me this error :

(Value does not Fall Within the Expected Range)

And I keep geting the error since the connection is int the projet.
If y delete the connection, I can use every other custom component from the Dll without problem.

And a more strange behaviour is that in the designer the connection do the validation process as I get the expected error when not configured.

Finally here is my post process code of the assembly :

@SET GACUTIL="C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools\gacutil.exe"  
@SET CONNECTIONSDIR150="C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Connections"  
@SET COMPONENTSDIR150="C:\Program Files (x86)\Microsoft SQL Server\150\DTS\PipelineComponents"  
@SET TASKSDIR150="C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Tasks"  
  
Echo -------------------------------------  
Echo Copying files 2019  
copy "$(TargetDir)$(TargetFileName)" %CONNECTIONSDIR150%  
copy "$(TargetDir)RestSharp.dll" %CONNECTIONSDIR150%  
copy "$(TargetDir)Newtonsoft.Json.dll" %CONNECTIONSDIR150%  
copy "$(TargetDir)$(TargetFileName)" %COMPONENTSDIR150%  
copy "$(TargetDir)RestSharp.dll" %COMPONENTSDIR150%  
copy "$(TargetDir)Newtonsoft.Json.dll" %COMPONENTSDIR150%  
copy "$(TargetDir)$(TargetFileName)" %TASKSDIR150%  
copy "$(TargetDir)RestSharp.dll" %TASKSDIR150%  
copy "$(TargetDir)Newtonsoft.Json.dll" %TASKSDIR150%  
  
Echo -------------------------------------  
Echo Installing $(TargetFileName) in GAC  
%GACUTIL% -if "$(TargetDir)$(TargetFileName)"  
Echo Installing RestSharp.dll in GAC  
%GACUTIL% -if "$(TargetDir)RestSharp.dll"  
Echo Installing Newtonsoft.Json.dll in GAC  
%GACUTIL% -if "$(TargetDir)Newtonsoft.Json.dll"  

Answer 1

Hi @Juan Jimenez ,

Could you help run VS2019 as administrator and then re-build the script for a try?

In addition, have you tried to upgrade the package?

In SQL Server 2019 (15.x), the Integration Services assemblies were upgraded to .NET 4.0. There is a separate global assembly cache for .NET 4, located in <drive>:\Windows\Microsoft.NET\assembly. You can find all of the Integration Services assemblies under this path, usually in the GAC_MSIL folder.

As in previous versions of SQL Server, the core Integration Services extensibility .dll files are also located at <drive>:\Program Files\Microsoft SQL Server\130\SDK\Assemblies.

Script Task and Script Component scripts that explicitly reference ADODB.dll may not upgrade or run on machines without SQL Server Management Studio or SQL Server Data Tools (SSDT) installed. In order to upgrade these Script Task or Script Component scripts, it is recommended that you remove the dependency on ADODB.dll. Ado.Net is the recommended alternative for managed code such as VB and C# scripts.

Details you may refer to upgrade-integration-services-packages

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Hi Zoe,
Thank you for the answer.

Not sure I anderstoud what you said, but I checked in C:\Windows\Microsoft.NET\assembly\GAC_MSIL and found my assembly with the correct version.
Even if I generate my assembly in administrator or not.

In my assembly I use only the version 15.0 of these files directely from C:\Windows\Microsoft.NET\assembly\GAC_MSIL:

And my packages are created directelly in VS 2019 so no need to upgrade.

While in designer mode everything works fine, the error appears only when I try tu run the package.
I suspect something like dtsdebug.exe does not find the assemblies or something like that.

Best regards
Juan

Answer 3

Here is a little update on this issue:

If I downgrade the connection from projet to package connection, now I get this message :
The connection type "sidJSON OAuth2.0" specified for connection manager "cnxPackSid2" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

Maybe that helps somone to help me :)