SSIS Custom Connection Manager

Question

I`m trying to create a custom connection manager for SQL Server Integration Services 2019, this component already works in SQL Server Integration Services 2012, but, after converting to SQL Server 2019 and changing DLLs of the same version, when I use the connection in Visual Studio, I get an error:

Connection type "XXX" specified for connection manager "XXX" was not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the name of the connection type

My code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;

namespace TesteCon
{
    [DtsConnection(
      ConnectionType = "COMTest",
      DisplayName = "Test COM Connection",
      Description = "Connection Test COM Services",
      //IconResource = "TesteCon.TesteCon.ico",
      ConnectionContact = "Test",
      UITypeName = "TesteConUI.TesteConUI,TesteConUI,Version=1.0.0.0,Culture=neutral,PublicKeyToken=2ce2b8238f2c8ed7")]
    public class TesteCon : ConnectionManagerBase, IDTSComponentPersist
    {
        //Public Key : 2ce2b8238f2c8ed7

        #region Get and Sets
        private const string CONNECTIONSTRING_TEMPLATE_RECEIVE = "MESSAGE=<message>;IDPEDIDO=<idpedido>;TYPE=<type>;";
        private const string CONNECTIONSTRING_TEMPLATE_ENVIO = "MESSAGE=<message>;";

        private string MessageTransaction = "";
        private string IdPedidoTransaction = "";
        private string TypeAction = "";

        [CategoryAttribute("ICOM Connection")]
        [Description("Caminho do arquivo ICOM.ini")]
        public string PathICOMIn { get; set; }

        [Category("ICOM Connection"),
        Description("Persiste conexão ativa")]
        public bool PersistentConnection { get; set; }

        public override string ConnectionString { get; set; }

        #endregion

        #region Creator
        public TesteCon()
        {
            PersistentConnection = false;
        }
        #endregion

        #region Overrides Connections

        public override DTSExecResult Validate(IDTSInfoEvents infoEvents)
        {
            if (!Directory.Exists(PathICOMIn))
            {
                infoEvents.FireError(0, "ICOM Connection", "Caminho informado para a conexão está inválido",
                    string.Empty, 0);
                return DTSExecResult.Failure;
            }

            return DTSExecResult.Success;
        }

        public override void ReleaseConnection(object connection)
        {

            base.ReleaseConnection(connection);
        }

        public override object AcquireConnection(object txn)
        {
            try
            {
                ConnectionString = "CODIGORETORNO=000;";
                return "OK";

            }

            catch (Exception)
            { 
                ConnectionString = "CODIGORETORNO=-999;";

                return null;
            }

            finally
            {
                ConnectionString = "Finally";
            }



            return base.AcquireConnection(txn);
        }

        public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
        {
            XmlElement rootElement = doc.CreateElement("TESCONMANAGER");
            doc.AppendChild(rootElement);

            XmlAttribute connectionStringAttr = doc.CreateAttribute("ConnectionString");
            connectionStringAttr.Value = ConnectionString;
            rootElement.Attributes.Append(connectionStringAttr);

            XmlAttribute userNameStringAttr = doc.CreateAttribute("PathICOMIn");
            userNameStringAttr.Value = PathICOMIn;
            rootElement.Attributes.Append(userNameStringAttr);

            XmlAttribute urlStringAttr = doc.CreateAttribute("PersistentConnection");
            urlStringAttr.Value = PersistentConnection == true ? "Sim" : "Nao";
            rootElement.Attributes.Append(urlStringAttr);
        }

        public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
        {
            // Checking if XML is correct. This might occur if the connection manager XML has been modified outside BIDS/SSDT
            if (node.Name != "TESCONMANAGER")
            {
                throw new Exception(string.Format("Unexpected connectionmanager element when loading task - {0}.", "TESCONMANAGER"));
            }
            else
            {
                // Fill properties with values from package XML
                this.PathICOMIn = node.Attributes.GetNamedItem("PathICOMIn").Value;
                this.PersistentConnection = node.Attributes.GetNamedItem("PersistentConnection").Value == "Sim" ? true : false;
                this.ConnectionString = node.Attributes.GetNamedItem("ConnectionString").Value;
            }
        }


        #endregion
    }

this code doesn't do anything, only returns "OK", just to test.

DLL: Microsoft.SqlServer.ManagedDTS Version: 15.0.0.0

I don't know what can cause this error and I can't find examples of custom connection managers for SSIS 2019.

Answer 1

Hi @Josivan Laskoski

Code writing is out of my ability, check Coding a Custom Connection Manager to see if it could give you some ideas.

Please also make sure that the TargetServerVersion of your SSIS project is SQL Server2019.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

try to re-sign the assembly,deploy it and install it in GAC.

then restart the computer, open your ssis project then try to add the connection manager.

further more.

check dts Version from the reference.

check the .netframework version.

i would like to use SSDT 2017 and SQL 2017 first to make it work, then 2019 later.