SSIS upgrade from 2008R2 to 2017 - script task fails when connecting to OLEDB data source

Question

I'm in the process of upgrading a data warehouse from SQL Server 2008R2 to 2017. After the upgrade, every script task is throwing the same error trying to connect to a logging database.

The task connects to a database via an OLEDB connection manager, then writes some meta data to a logging table. It's failing on making the initial connection. I'm hoping it's a fairly simple invocation error, but I'm a SQL guy, not a VB guy, and I'm not seeing the issue.

I found this question, Missing library to reference OLEDB connection types, but the script isn't invoking the AcquireConnection() method, so I don't think it's applicable. Or if it is, I'm not seeing how.

This is the code, through the point of failure.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Collections
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
  Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

  Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
  End Enum

    Public Sub Main()
        Dim fireAgain As Boolean
        Dim pkgExecVar As Variables
        Dim cm As ConnectionManager
    Dim cmParam As IDTSConnectionManagerDatabaseParameters100
    Dim conn As OleDb.OleDbConnection
    Dim cmd As OleDbCommand = New OleDbCommand()

    pkgExecVar = Nothing
    cm = Dts.Connections("Configuration_Metadata_Logging")
    cmParam = CType(cm.InnerObject, IDTSConnectionManagerDatabaseParameters100)
    conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)

When I step through the code, the second line from the bottom throws an exception.

cmParam = CType(cm.InnerObject, IDTSConnectionManagerDatabaseParameters100)

System.InvalidCastException HResult=0x80004002 Message=Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{624862CB-55F9-4A92-965F-62BC4935296A}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

If there's anything else I can add to the question to help, please let me know.

EDIT: The OLEDB connection manager is using SQLNCLI11.1. Other components of the package (Execute SQL & Dataflow Tasks) are able to connect successfully.

EDIT II: In order to make this more searchable, here's the initial error the package throws, before adding a breakpoint to the script task and stepping through it.

Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Answer 1

Update. I found the source of that code, and it's a blog post by Matt Mason called Accessing OLEDB Connection Managers in a Script. It's still undocumented and probably got broken some time between SQL 2008 and 2017.

The supported ways to do this are documented here: Connecting to Data Sources in the Script Task

The only ways to get a System.Data.OleDbConnection in a script task are

1) To use an ADO.NET Connection Manager and configure it to use the ADO.NET Provider for OleDb, like this:

 Dim cm As ConnectionManager = Dts.Connections("Configuration_Metadata_Logging_adonet_oledb")
 Dim conn As OleDb.OleDbConnection = CType(cm.AcquireConnection(Nothing), OleDb.OleDbConnection)

2) Use an OleDb Connection Mamanger and use its ConnectionString to create a new OleDbConnection (which you are also responsible for closing in your Script task). Like this:

Dim cm2 As ConnectionManager = Dts.Connections("Configuration_Metadata_Logging_oledb")
Using conn2 As New OleDb.OleDbConnection(cm2.ConnectionString)
    conn2.Open()

End Using