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
has four namespaces:
Seeing this, we changed the using statement to specify the Sql2012 version and it worked! However, some big questions remain:
- Why is this needed now (it wasn't needed when using VS 2012)?
- 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!"
- 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.