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 ] 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?