How to use a SQL Server Integration Services (SSIS) custom Connection Manager programmatically?

Question

I am trying to programmatically create an SSIS package from a C# console application that uses a 3rd party Custom Connection Manager for Kafka like so:

static void Main(string[] args) 
{ 
    Application app = new Application(); 
    Package pkg = new Package(); 
    ConnectionManager cmKafka = pkg.Connections.Add("KAFKACS"); 
...

which fails with:

Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException
The connection type "KAFKACS" specified for connection manager ... is not recognized as a valid connection manager type.

Adding this manager to an Integration Services Project from Visual Studio 2017 works just fine, however.

I am new to SSIS, C# and Windows development. Here's what I've tried so far:

• Add GAC references to KAFKACS assemblies to my console application.
• Force 64bit execution which results in Microsoft.SqlServer.DTSRuntimeWrap not found.
• Use reflection to try to figure out what architecture and for what .NET framework version the Kafka DLLs I reference target (looks like MSIL).
• Create versions of the same application using previous versions of .NET target framework.
• Try to manually create an instance of the Kafka manager as shown here:

var rawKafka = new KafkaConnectionManager(); 
ConnectionManager cmKafka = (???) rawKafka; 
pkg.Connections.Join(cmKafka); 

• Do I need to register the KAFKACS component in order for the call to Connections.Add to succeed?
• Are the error messages misleading and I am missing other, perhaps Kafka specific assemblies I need to reference?
• Can I manually create a instance of the KafkaConnectionManager and use it with Connections.Join?

Answer 1

To answer my question, the issue had been that the 3rd party Connection Manager was build for SQL Server 2017 (v14) but the console application referenced the ManagedDTS assembly for 2016 (v13).

The call to pkg.Connections.Add("KAFKACS") could not find the DLLs since the 3rd Party installer had installed them in C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Connections (where 140 corresponds to v14) and not in \130\DTS\Connections where v13 of ManagedDTS would expect to find them.