将任务放入序列容器后无法连接到数据库 [英] cannot connect to database after putting tasks in sequence container
问题描述
我有一个完美运行的包，直到我决定将它的一些任务放在一个序列容器中(更多关于我为什么要这样做 - 如何在我的情况下进行 SSIS 交易?).
I had a package that worked perfectly until i decided to put some of its tasks inside a sequence container (More on why I wanted to do that - How to make a SSIS transaction in my case?).
现在，我不断收到错误消息 -
Now, i keep on getting an error -
[Execute SQL Task] Error: Failed to acquire connection "MyDatabase". Connection may not be configured correctly or you may not have the right permissions on this connection.
为什么会发生这种情况，我该如何解决?
Why could this be happening and how do I fix it ?
推荐答案
我开始编写自己的示例来回答您的问题.然后我记得我星期六在新罕布什尔州的 SQL 会议上谈话时遇到了 Matt Mason.他是 SSIS 的 Microsoft 项目经理.
I started writing my own examples to reply to your question. Then I remember that I met Matt Mason when I talked at a SQL Saturday in New Hampshire. He is the Microsoft Program Manager for SSIS.
虽然我在 2009 年到 2011 年间花了 3 年时间只写 ETL 代码，但我认为 Matt 有一篇文章.
While I spent 3 years between 2009 and 2011 writing nothing else but ETL code, I figured Matt had an article out there.
http://www.mattmasson.com/2011/12/design-pattern-avoiding-transactions/
这是您发现的方法和错误的高级摘要.
Here is a high level summary of the approaches and the error you found.
[错误]
您发现的错误与 MSDTC 出现问题有关.这必须配置并正常工作，没有任何问题.常见问题是防火墙.看看这篇文章.
The error you found is related to MSDTC having issues. This must be configured and working correctly without any issues. Common issues are firewalls. Check out this post.
[解决方案 1] - 在包、任务或容器级别使用事务.
[SOLUTION 1] - Use transactions at the package, task or container level.
某些数据提供程序不支持 MSDTC.有些任务不支持事务.这可能会降低性能，因为您要添加一个新层来支持两阶段提交.
Some data providers do not support MSDTC. Some tasks do not support transactions. This may be slow in performance since you are adding a new layer to support two phase commits.
http://technet.microsoft.com/en-us/library/aa213066(v=sql.80).aspx
[解决方案 2] - 使用以下任务.
[SOLUTION 2] - Use the following tasks.
A - BEGIN TRAN(执行 SQL)
A - BEGIN TRAN (EXECUTE SQL)
B - 您的数据流
C - 测试返回码
1 - GOOD = 提交(执行 SQL)
1 - GOOD = COMMIT (EXECUTE SQL)
2 - 失败 = 回滚(执行 SQL)
2 - FAILURE = ROLLBACK (EXECUTE SQL)
您必须在连接上将 RetainSameConnection 属性设置为 True.
You must have the RetainSameConnection property set to True on the connection.
这会强制所有调用都通过一个会话或 SPID.所有事务管理现在都在服务器上.
This forces all calls thru one session or SPID. All transaction management is now on the server.
[解决方案 3] - 编写所有代码以使其可重新启动.这并不意味着您出去使用检查点.
[SOLUTION 3] - Write all you code so that it is restartable. This does not mean you go out and use check points.
一种解决方案是始终使用 UPSERTS.插入新数据.更新旧数据.删除只是表中的一个标志.这种模式允许多次执行失败的作业并达到相同的最终状态.
One solution is to always use UPSERTS. Insert new data. Update old data. Deletes are only a flag in a table. This pattern allows a failed job to be executed many times with the same final state being achieved.
另一种解决方案是通过将所有错误行放入医院表中进行人工检查、更正和插入来处理所有错误行.
Another solution is to handle all error rows by placing them into a hospital table for manual inspection, correction, and insertion.
为什么不使用数据库快照(跟踪刚刚更改的记录)?在 ETL 作业之前拍摄快照.如果发生错误，请从快照还原数据库.最后一步是从系统中删除快照以清理房子.
Why not use a database snapshot (keeps track of just changed records)? Take a snapshot before the ETL job. If an error occurs, restore the database from the snapshot. Last step is to remove the snapshot from the system to clean up house.
简而言之，我希望这些想法足以帮助您.
In short, I hope this is enough ideas to help you out.
虽然交易选项不错，但它确实有一些下跌.如果你需要一个例子，就 ping 我.
While the transaction option is nice, it does have some down falls. If you need an example, just ping me.
真诚的
J
这篇关于将任务放入序列容器后无法连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
