Weird issue with 2012 package and Visual Studio 2017

  • gbritton1

    SSCertifiable

    Points: 6520

    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.
  • Martin Schoombee

    SSCoach

    Points: 19020

    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: 6520

    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: 19020

    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

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

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