用于错误处理的 SSIS 本地嵌套事务
我们计划不在 SSIS 中使用 MSDTC 进行事务管理，而是希望通过开始事务和提交事务来使用本地事务。从设计角度来看，除了一个问题之外，它似乎还不错。当 SSIS 包在任何任务中失败时，我们有一个错误事件处理程序，它将记录插入到错误表中。因此，现在发生的情况是，当 SSIS 回滚事务时，它也会回滚错误处理例程。但我不确定如何精细地控制这一点，因为当我在包执行开始时启动事务时，嵌套事务将无法工作。
有什么想法吗？
We are planning NOT to use MSDTC for transaction managment in SSIS and instead want to use local trsnactions using Begin transaction and commit transaction. From design wise it seems to be OK except one issue. When SSIS package fails in any of the task we have an error event handler which inserts records into errors table. So now what is happening is when SSIS rolls back the transaction it is rolling back the error handling routine as well. But I am NOT sure how I can control this granularly since nested transactions are NOT going to work as I start the transaction at the begining of package execution.
Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是MSDN 上的文章，解释了如何使用 TRY-CATCH 块支持错误处理的事务提交。
关于您对嵌套事务的评论，它们实际上并不存在于 SQL Server 中。请参阅此来自 Paul Randal 的博客文章。
Here's an article on MSDN that explains how to use TRY-CATCH blocks with TRANSACTION COMMITT that supports error handling.
Regarding your comment about nested transactions, they don't actually exist in SQL Server. Please see this blog post from Paul Randal.
我切换到 MSDTC 来处理 SSIS 中的这些事务和错误处理。这种新方法非常干净，只需将 SSIS 中的相关流程标记为需要或不需要 txn 即可。我将错误处理例程标记为“不支持 txn”，因为我不希望它在出现错误时回滚。现在一切看起来都很好，除了我们案例中的基础设施开销，因为我们将解决方案捆绑到各个客户工作站。
非常感谢
I switched to MSDTC to handle these transactions and error handling in SSIS. This new approach is very clean, just mark the relevant flows in SSIS as txn required or not and you are done. I marked the error handling routine as 'txn not supported' since I didn't want it to rollback in case of errors. Everything looks fine now except the infrastructure overhead in our case since we are bundling the solutions to individual clients workstations.
Many thanks