There is an SSIS package with Required
transaction on the package level and Supported
on the tasks level. The main part of the package is deleting the data and inserting new data:
Here the Delete block deletes everything from six tables, and the Parse block loads six files to the six tables.
If the Delete uses delete from dbo.table
, then everything works fine, but is slow.
But if Delete uses truncate table dbo.table
instead, the deletion takes no time, but the package becomes blocked when it comes to the Parse
task. Would just sit around doing nothing.
At this moment on the server side I can see an SSIS spid being blocked by spid -2
, which is "Orphaned distributed transaction." It would appear that used to represent the truncating connection. This connection hangs around, and the package is blocked by the locks it placed.
If I switch the only used SQL Server Connection manager to RetainSameConnection = True
, then the package fails as soon as it enters the Parse task. The task asks the manager for a connection, and the manager responds with
[SQL Server Destination [471]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection Manager" failed with error code 0xC001A004. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Is there a way to set up the package so that it uses truncate
and does not block itself?
Best Answer
This is due to blocking caused by the
delete
task accessing the metadata from the database during the Pre-Execution phase.The locks can be found using
sys.dm_tran_locks
and it will have-2
value as OP founded. More details could have been found out using Profiler.The blocking can be resolved by changing the
ValidateExternalMetadata
property toFALSE
or as OP mentioned using aDELETE
statement.Reference: KB2253391