Workarounds for ReadUncommitted Isolation level in

问题:

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

http://connect.microsoft.com/SQLServer/feedback/details/498891/ssis-setting-isolationlevel-to-readuncommitted-still-uses-read-committed#details

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

回答1:

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)

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

here are both my tables empty:

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

回答2:

Use SQL Server Native Client Provider (if your target platform in MS-SQL) and setting up the isolation level to see if it is work ?