I'm trying to wrap a TRUNCATE TABLE and a load of 14.7M rows in a single SSIS component. (I've already adjusted the TransactionOption=Required, and VerifyExternalMetaData=False, and set up MSDTC.)
At the end of the 22 minute load, the SSIS package reports that the load failed ("The transaction has already been aborted"), and then SPID #17 kicks into gear, with command = "TASK MANAGER", and blocking queries. Try as I might, I can't get any insight into what this system spid is actually doing.
At first I suspected an Auto Stat Update, so I turned off Auto Stats Updates.
I thought it might be the rollback of the load, but I think rollbacks are under the original SPID, and actually report as "ROLLBACK".
(Obviously, I'd like to know why the package is failing, but I haven't given up on figuring that out on my own. However, having to sit through 60-120 minutes of this mystery blocker prevents me from making quick progress, so I'd like to know the answer here).
Thanks in advance for any insight!