SSIS 在设计时获取连接，但在同一台机器上调试期间失败 [英] 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(从 2016 安装 cd 开始，然后尝试 2014 cd，然后尝试从 MS 下载)
为了排除其他配置问题，我创建了一个新的连接管理器.输入所有信息.除非我使用 FQDN，否则不会填充数据库列表.测试连接成功.执行使用连接管理器的任务.在验证步骤中挂起 30 秒，然后失败:
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.
我尝试了 Microsoft OLEDB Driver for SQL Server，但没有成功.我能想到的这个服务器唯一奇怪的地方是它有一个实例名称.这会影响这样的事情吗?在装有 Visual Studio 2017 的 Win7 机器上，它从来没有给我带来麻烦.我真的不知道如何调试它.
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.
有趣的是，我也无法使用 SQLCMD 连接到此服务器.我收到了类似的错误，但似乎是一个完全不同的驱动器:
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.
推荐答案
尝试修改您的连接字符串以包含 Connect Timeout=45;
Try amending your connection string to include Connect Timeout=45;
默认连接超时为 15 秒，因此我们将超时时间增加三倍.
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
位于可用性组中的服务器的超时.但仅在节点的一侧.并且不总是但经常足以毁了我的早晨(由于批处理作业失败导致愤怒的用户).堆栈中的某些手势导致连接无法完全打开.无论是 DNS、网络、目标服务器太忙还是不知道是什么，我的工具带中都没有足够的工具来解决它.但由于错误的根源在于无法足够快地登录，因此增加超时时间似乎是一个合理的方法.
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.
在您的情况下，您需要指定 FQDN 和 SQLCMD 的事实显示出相同的症状，这似乎是网络问题，但超出了我的专业知识.然而，命名实例不应该有任何意义.过去 N 年我的客户只有命名实例.
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
这篇关于SSIS 在设计时获取连接，但在同一台机器上调试期间失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
