Creating SSIS Custom Component - PreExecute method cannot get connection manager

Question

I had trying my hands on creating a SSIS custom component to retrieve data from Salesforce. I having the custom connection manager project working and verified, and the custom source works and acquires connection until it gets to the PreExecute method. I noticed that setting breakpoints on the PreExecute methods don't work so I used the MessageBox.Show() and found that upon executing to retrieve data into the the SSIS Outputs (OutputCollection), the connection manager specified is always null. However the connection manager and the source component works fine up to the SetComponentProperty method, as I can add Output columns to the SSIS package.

Error I get is "[MyCustomComponent Source [8]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at MyCustomComponent.MyCustomComponentSource.MyCustomComponentSource.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)"

Code is ...

 [DtsPipelineComponent(DisplayName = "MyCustomComponent", ComponentType = ComponentType.SourceAdapter, IconResource = "MyCustomComponent.Resources.Icon1.ico")]
    public class MyCustomComponent : PipelineComponent
    {
        private ForceClient sfConnection;
        private SFConnectionManager.SFConnectionManager ksConnectionManager;

        private string sfQuery = string.Empty;
        //private string sfQuery = "SELECT id, name, AccountNumber, BillingCity FROM Account";

        public int[] mapOutputColsToBufferCols;

        public override void AcquireConnections(object transaction)
        {
            //base.AcquireConnections(transaction);

            if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
            {
                ConnectionManager connectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetWrapper(
                  ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

                this.ksConnectionManager = connectionManager.InnerObject as SFConnectionManager.SFConnectionManager;

                if (this.ksConnectionManager == null)
                    throw new Exception("Couldn't get the Salesforce connection manager, ");

                this.sfConnection = this.ksConnectionManager.AcquireConnection(transaction) as ForceClient;
            }
        }
        public override void ReleaseConnections()
        {
            if (ksConnectionManager != null)
            {
                this.ksConnectionManager.ReleaseConnection(sfConnection);
            }
        }
        public override void ReinitializeMetaData()
        {
            this.ComponentMetaData.RemoveInvalidInputColumns();
            base.ReinitializeMetaData();
        }
        public override DTSValidationStatus Validate()
        {
            return base.Validate();
        }
        public override IDTSCustomProperty100 SetComponentProperty(string propertyName, object propertyValue)
        {
            if (propertyName == "SOAQ" && ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count == 0)
            {
                AddOutputColumns(propertyValue.ToString());
            }

            return base.SetComponentProperty(propertyName, propertyValue);
        }
        public override void ProvideComponentProperties()
        {
            // Reset the component.
            base.ProvideComponentProperties();
            base.RemoveAllInputsOutputsAndCustomProperties();
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

            IDTSCustomProperty100 sf_query = ComponentMetaData.CustomPropertyCollection.New();
            sf_query.Name = "SOAQ";
            sf_query.Description = "Salesforce Connector Query";

            //Commenting query out as it will be provided manauuly and later entered in from form.
            sf_query.Value = sfQuery;

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

            IDTSRuntimeConnection100 connection = ComponentMetaData.RuntimeConnectionCollection.New();
            connection.Name = "K Salesforce";
        }
        public override void PreExecute()
        {
            base.PreExecute();

            IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
            mapOutputColsToBufferCols = new int[output.OutputColumnCollection.Count];

            for (int i = 0; i < ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count; i++)
            {
                // Here, "i" is the column count in the component's outputcolumncollection
                // and the value of mapOutputColsToBufferCols[i] is the index of the corresponding column in the
                // buffer.
                mapOutputColsToBufferCols[i] = BufferManager.FindColumnByLineageID(output.Buffer, output.OutputColumnCollection[i].LineageID);
            }
        }

Note upon when it gets to PreExecute, the connection variables sfConnection and ksConnectionManager resets to null. I am not sure if creating a new instance in PreExecute will help (I tried) but my C# skills are limited as I do not use it much professionally.

I tried several suggestions online such as

• Changed Platform target to Any CPU on VS (I also tried x86 and x64).
• Change ProtectionLevel on SSIS package.
• Changed delay validation property at package level to True.
• Set 'run64bitRuntime' to False

I am using SQL Server 2019 with Visual Studio 2019

Answer 1

This happens to be by design or a bug on SSIS. Connection is always lost or resets to null once it gets to the pre execution phase (runtime). Re-instantiating the connection seems to help and maybe the only way to go on current versions of Visual Studio. From this link Custom ConnectionManager always returns null at runtime it shows others having the same issue.