SSIS 包中 ReadUncommitted 隔离级别的解决方法 [英] Workarounds for ReadUncommitted Isolation level in an SSIS package

问题描述

SSIS中的ReadUncommitted IsolationLevel是Microsoft承认的以下错误，但如下所述是无法修复".

The ReadUncommitted IsolationLevel in SSIS is a bug acknowledged by Microsoft for the following but 'Wont fix' as described below.

相同的解决方法是什么?

What would be the workaround(s) for the same?

推荐答案

是的，但是必须在执行之前通知源上的sql命令，而不是选择表并设置隔离级别:

yes, but you have to inform the sql command on your source instead of selecting a table and set the isolation level before the execution:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select * from test_isolation

并且该软件包应选择可序列化的隔离级别(我认为该错误可能是由于选择了组件上未提交的读取这一事实)

and the package should have the serializable isolation level selected (I think the bug may be on the fact of selecting read uncommited on the component)

请参阅此示例.我有一个程序包可以将值从表 test_isolation 复制到表 test_isolation_destination

See this example. Im have a package to copy values from table test_isolation to table test_isolation_destination

这两个表都为空:

然后我开始一个事务并在下面运行插入命令以在 test_isolation

then I started a transaction and ran the insert command bellow to insert a row on test_isolation

此行是脏行，因为尚未提交事务.

this row is a dirty row because the transaction is not committed yet.

然后，我运行了程序包，如您所见，复制了一行:

Then, I ran the package and as you can see one row was copied:

然后，我回滚了事务，如您所见，该行已从源表中清除，但未从目标表中清除.

then, I rolled back the transaction and as you can see the row was cleaned from the source table but not from the destination table.

这证明程序包以读取的未提交隔离级别运行

That proves that package ran with read uncommitted isolation level

这篇关于SSIS 包中 ReadUncommitted 隔离级别的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！