4

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:

enter image description here

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?

Improve this question
10
  • What about switching strategies altogether? Have a look at my answer here for what I mean. (The question asks about moving data, but the same sort of strategies can be applied to deletes as well.)
    – Jon Seigel
    Aug 7, 2013 at 12:38
  • @GSerg have you tried setting sql task's property ByPassPrepare to False and package property DelayValidation to True ? Also, is the truncate table when you execute outside SSIS, still blocking ? Any other process running at the same time as Truncate requires SCH-M lock for deallocating the table allocation units.
    – Kin Shah
    Aug 7, 2013 at 13:59
  • I've used a truncate statement on the tables I'm loading data to with the transaction options as you describe. I'll let my brain cycle and see if any insight strikes
    – billinkc
    Aug 7, 2013 at 14:01
  • @JonSeigel An interesting solution, will tuck away for future use, but will probably not apply here.
    – GSerg
    Aug 7, 2013 at 14:30
  • 1
    Found this KB article suggesting ValidateExternalMetadata to be set to FALSE. support.microsoft.com/kb/2253391
    – Kin Shah
    Aug 7, 2013 at 16:05

1 Answer 1

Reset to default
4

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 to FALSE or as OP mentioned using a DELETE statement.

Reference: KB2253391

Improve this answer
1
  • 1
    While the asnwer solves the problem, it does not really connect the dots nicely (and while I think I did connect them, I'm not entirely sure). So let me restate: The problem is that truncate will take schema-stability locks in addition to data locks, whereas delete will only take data locks. When later SSIS goes to validate the schema, it hits the taken schema-stability lock and waits because the connection that verifies metadata is intentionally not enlisted in the distributed transaction. With delete there is no problem because no schema locks were taken in the first place. Correct?
    – GSerg
    Aug 7, 2013 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.