Weird issue with 2012 package and Visual Studio 2017

Question

Weird issue with 2012 package and Visual Studio 2017

gbritton1

SSCertifiable

Points: 6522
More actions
April 10, 2019 at 5:50 pm

#3095873
In one of the projects I work on, we have several packages that pull from Oracle and write to a SQL Server 2012 database. They've been working for years. Recently we got new laptops and we're running VS 2017 with all the usual plugins and extensions for SSDT-BI projects. While working on one package, we discovered that the connection to Oracle no longer worked from within a script task. There is a section of code like this:
```
ConnectionManager cm = Dts.Connections["Conn_Oracle"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
```
After rebuilding with VS 2017, the cmParms variable receives null, which of course makes the package fail when the code tries to deference that. We discovered, quite by accident, that the assembly
```
   C:\WINDOWS\Microsoft.Net\assembly\GAC_64\Microsoft.SQLServer.DTSRuntimeWrap\v4.0_14.0.0.0__89845dcd8080cc91\Microsoft.SQLServer.DTSRuntimeWrap.dll
```
has four namespaces:
```
Microsoft.SqlServer.DTSRuntimeWrap
Microsoft.SqlServer.DTSRuntimeWrap.Sql2012
Microsoft.SqlServer.DTSRuntimeWrap.Sql2014
Microsoft.SqlServer.DTSRuntimeWrap.Sql2016
```
Seeing this, we changed the using statement to specify the Sql2012 version and it worked! However, some big questions remain:
1. Why is this needed now (it wasn't needed when using VS 2012)?
2. The version-specific namespace is documented here: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.wrapper.sql2012 but I can't find anything saying "use this if you are building with VS 2017 for a SQL Server 2012 project!"
3. When you build a new script component in VS 2017 for a SQL Server 2012 SSIS project, the version-specific namespace is not automatically used. Any clues why not? Certainly, in our recent experience, this will not work under our scenario.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19127 More actions · Answer 1

I stand to be corrected, but I believe it's due to the .NET framework change for VS 2017 (and later versions of SSIS). By changing the namespace reference, you're just forcing the script task to be compiled (and executed) by a different version of .NET and not the default which is clearly not working with the syntax you have.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

You could be correct, but I am frustrated by the obscure nature of the failure (the null reference), the lack of documentation around the issue and the fact that VS 2017 creates the boiler plate for a new script task without the version reference on the namespace, even though the project is marked as 2012.

Martin Schoombee SSCoach Points: 19127 More actions · Answer 3

I hear you, and deal with similar frustrations on a daily basis. The way in which SSIS parameters and variables are referenced in the later versions of .NET has changed. It's mostly a syntax change, but I believe that is the root cause of your failure as it is not able to access the variable container to get the value. Fun times.

Edit: I should have said "object model" instead of syntax, because that's what really changed in .NET

This reply was modified 5 years, 7 months ago by Martin Schoombee.
This reply was modified 5 years, 7 months ago by Martin Schoombee.