Recently, I experienced an issue where a custom destination component in SQL Server Integration Services (SSIS) was failing in Visual Studio. The component worked fine when the SSIS package was deployed to the server and worked at design time in designing the package. Very strange to say the least. Why would the package success at design time in Visual Studio, execute successfully when deployed to the target SQL Server, but fail at runtime in Visual Studio when the developer was testing? After much troubleshooting, we finally found an answer. Details below on the error and solution for anyone who encounters this!
Version Info
Visual Studio 2017 (15.9)
SQL Server 2019
Note: this issue likely could occur in Visual Studio 2019 as well. We originally started to use Visual Studio 2019 but abandoned it was that version as SSIS as an extension, which we thought could be a factor in our original error.
Error
[SSIS.Pipeline] Error: Cannot find the connection manager with ID "{13E04AC8-6375-4328-A1CC-71AAB3AAAF21}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "MyCustomComponent.Connections[CustomConnectionManager]" in the connection manager collection of "MyCustomComponent". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
Solution
Ultimately, the solution required installing SQL Server 2019 and Integration Services into the same VM where the developer was working in Visual Studio. It appears that the Visual Studio installer was missing required libraries and executables to pick up the custom connection manager that the custom destination component was using. This explains why the package would succeed on the server as the full SQL Server installation contained all the necessary references.