Custom ConnectionManager always returns null at runtime

Question

I have written a very simple custom connection manager inheriting from ConnectionManagerBase. My acquireconnections and release works as it should in design time on my source component. The issue is when I try and execute it in Visual Studio. I associate my connection with my component in design time and the value persists as it should. But when my source component calls acquireconnection and then checks the RuntimeConnectionCollection[0].ConnectionManager, it is always null. During design time it is there and establishes a connection fine but for the life of me I can't figure out what I am missing to make it work during run time. I know it has to be something simple but I have read code for ssis connection managers and source/destination components until I am blue in the face and can't see what I am missing. Does my savior exist on this forum? I am running Visual Studio 2019 with 3.12 of SSIS plugin. Framework version is 4.7.2 to compile on any cpu. Please let me know if anyone has any questions about anything and I am happy to answer.

Answer 1

Hi @CharlesGriggs-1322 ,

Could you please share the example script of the Custom Connection Manager?

[DtsConnection(ConnectionType = "SQLCS",
DisplayName = "SqlConnectionManager (CS)",
Description = "Connection manager for Sql Server",
UITypeName = "SqlConnMgrUICS.SqlConnMgrUICS,SqlConnMgrUICS,Version=1.0.0.0,Culture=neutral,PublicKeyToken=<insert public key token here>")]
public class SqlConnMgrCS :
ConnectionManagerBase
{
. . .
}

Please refer to the following links:
1.Creating a Custom Connection Manager
2.Developing a User Interface for a Custom Connection Manager
3.Biml alternatives: Building SSIS packages programmatically using ManagedDTS

Best regards,
Mona

----------

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks for your response. I have put together a very simple sample to reproduce my problem. It is a ConnectionManager and a CustomSource component that does nothing. When I debug I get the exact same behavior is my code. ConnectionManager establishes a connection fine at design time and the Runtime collection that holds the connection manager is null at run time. My code is below. It is this line if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null) that runs during AcquireConnection of my source component and the RuntimeConnectionCollection[0].ConnectionManager is always null. All my reading says the reason this happens is the developer has not set the connection during design time but that isn't the case. I can set the connection manager on my component, save and reopen visual studio and it saves fine. Then debug in design time and the connection establishes. Only during runtime does it not work. Please save me. :)

ConnectionManager Class

    [DtsConnection(ConnectionType = "HTTPConnection",
            DisplayName = "Http Connection Manager")]
    public class HttpConnectionManager: ConnectionManagerBase
    {
        private HttpClient client;

        [CategoryAttribute("Url")]
        [Description("Url for HTTP Connection")]
        public string ServerUrl { get; set; }

        public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
        {
            if (string.IsNullOrWhiteSpace(ServerUrl))
            {
                return DTSExecResult.Failure;
            }
            return DTSExecResult.Success;
        }

        public override object AcquireConnection(object txn)
        {
            var client = new HttpClient();
            return client;
        }

        public override void ReleaseConnection(object connection)
        {
            if (client != null)
            {
                var client = connection as HttpClient;
                client.Dispose();
                client = null;
            }
        }
    }

Source Component

    [DtsPipelineComponent(DisplayName = "Custom Source",
    ComponentType = ComponentType.SourceAdapter,
    Description = "Custom Source Component Test")]
    public class CustomSourceComponent: PipelineComponent
    {
        private HttpConnectionManager httpConnection;
        private HttpClient client;

        public override void ProvideComponentProperties()
        {
            // Reset the component.
            base.RemoveAllInputsOutputsAndCustomProperties();
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

            IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
            output.Name = "Output";

            IDTSOutputColumn100 column1 = output.OutputColumnCollection.New();
            IDTSExternalMetadataColumn100 exColumn1 = output.ExternalMetadataColumnCollection.New();

            column1.Name = "HTML";
            column1.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 4000, 0, 0, 0);

            IDTSRuntimeConnection100 connection = ComponentMetaData.RuntimeConnectionCollection.New();
            connection.Name = "Http Connection";

        }

        public override void AcquireConnections(object transaction)
        {
            if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
            {
                ConnectionManager connectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetWrapper(
                  ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

                this.httpConnection = connectionManager.InnerObject as HttpConnectionManager;

                if (this.httpConnection == null)
                    throw new Exception("Couldn't get the HttpConnectionManager instance");

                client = this.httpConnection.AcquireConnection(transaction) as HttpClient;
            }
        }

        public override void ReleaseConnections()
        {
            if (this.httpConnection != null)
            {
                this.httpConnection.ReleaseConnection(client);
                client = null;
            }
        }
    }

Answer 3

@Monalv-MSFT I have provided the code as requested. Honestly I think you will find there is nothing wrong with my code but maybe I am wrong. I am starting to feel like it is something to do with version or cpu targeting. I have tried a lot of different things though and don't want to spend anymore time guessing since this has been figured out 100 times over.

Answer 4

I have a little more information to provide. I tried to get an excel connection manager working and was getting similar behavior to my custom so I googled the error what solved it for someone was going to the project properties of the SSIS project under debugging and select false under Run64BitRuntime. That fixed the excel connection manager issue so I figured it was related so I tried mine and got a new error.

Cannot find the connection manager with ID in the connection manager collection due to error code 0xC0010009.

yay, progress, at least it can see the connection manager id that I know persists just fine. Now I get an error and it can't debug because I assume the execution never starts. My breakpoint is never hit that I set on pre execute and I get that error in the progress tab. The link below is someone who has the exact same issue that I am having. Creating a custom connection manager and getting the error above. I found many reasons the error above can surface but the OP below is the only reference to my exact same issue. The solution was to downgrade SSDT from 17 to 16.5. He was using VS 2017. I am using 2019 and there doesn't appear to be a stand alone installation of SSDT so not sure what version VS 2019 ships with. So I haven't tried this yet but wanted to get some feed back from someone hopefully as I believe this is my issue. Effectively I think my installation is screwed up. I did a vs install of 2019 and then sql 2019 with the latest version of integration plugin from the VS market place.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ad523249-7127-4112-b5ba-990de9429b2a/ssis-custom-component-connection-manager-and-data-source-adapter?forum=sqlintegrationservices

Answer 5

Hello @ManicFarmer ,

did you find a solution to your problem with the custom connectionManager for SSIS ?

Best regards,
Alex