SSIS Acquires connection at design time but fails during debug on same machine

Banging my head on the wall for this one. Package with many database connections. All databases from one particular server are giving me issues.

• Visual Studio 2019
• SQL Server 2012
• Windows 10
• SQLNCLI (from 2016 install cd, then tried 2014 cd, then tried download from MS)

To rule out other configuration issues, I created a new connection manager. Put in all the info. Database list won't populate unless i use a FQDN. Test connection succeeds. Execute a task that uses the connection manager. Hangs for 30 seconds on validation step, then fails with:

Error: 0xC020801C at MyDataFlow, MyComponent [12]: SSIS Error Code 
DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the 
connection manager "mydatabase" failed with error code 
0xC0202009.  There may be error messages posted before this with more information on why the 
AcquireConnection method call failed.

I've tried the Microsoft OLEDB Driver for SQL Server with no luck. The only odd thing about this server that I can think of is that it has an instance name. Would that affect something like this? It never gave me trouble on my Win7 machine with Visual Studio 2017. I'm truly at a loss as to how to debug this.

Interestingly enough, I cannot connect to this server with SQLCMD either. I get a similar error but a completely different drive it seems:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process 
due to delay in opening server connection.

Try amending your connection string to include Connect Timeout=45;

The default Connection Timeout is 15 seconds so we triple the timeout time.

Why it's happening, I don't know.

But that's never stopped me from speculating. In my situation, I was getting

Unable to complete login process due to delay in opening server connection

timeouts for a server that was in an availability group. But only on one side of the node. And not consistently but often enough that it'd ruin my morning (due to batch jobs failing resulting in irate users). Something in the stack gestures wildly about was causing the connection to fail to open completely. Whether it's DNS, network, the target server being too busy, or who-knows-what, I didn't have enough tools in my toolbelt to resolve it. But since the root of the error was it couldn't get logged on fast enough, increasing the timeout period seemed a reasonable approach.

In your case, the fact that you need to specify the FQDN and SQLCMD show the same symptoms, it seems like there's something networkish at play but beyond my expertise. The named instance however shouldn't have any bearing. My client for the past N years has nothing but named instances.

And future readers, the difference between Connection Timeout vs Command Timeouts

https://blogs.msdn.microsoft.com/docast/2018/10/11/connection-timeout-and-command-timeout-in-sql-server/