Monday, October 15, 2007

SSIS's interaction with DTC

I encountered some locking problems while developing an SSIS package today. The purpose of the package is to upload new content from flat file to a table. It contains basically two control flow tasks - one clearing the table (TRUNCATE TABLE) and another one filling it with a data flow task - that require a transaction. But, while executing, the package blocked at the start of the data flow task.

Investigating the blocking, I encountered some SPID -2 owned locks in sys.dm_tran_locks, thus blocking the data flow task from retrieving the necessary metadata from the database at the PreExecute event. I fiddled around with the setting of the TransactionOption and IsolationLevel of the different components, but nothing resolved my problem adequately. With the help of profiler however, I could determine that SSIS did not propagate the DTC transaction - started for the SQL task - to the Data Flow task.

The blocking can be resolved by either changing the ValidateExternalMetadata attribute of the OLE DB Destination or changing the TRUNCATE TABLE of the SQL task into a DELETE statement.

The reason why both of these solutions have such an impact remains unclear to me. It's true that each of these modifications affect the locks that are required by the package, but that doesn't explain the fact that the Data Flow task was not enlisted appropriately into MSDTC.


Martin said...

Came across the same issue except all transaction on a particular table where blocked (Tablock). In my case our SSIS packages (master and children) invoked by a SQL Agent job failed but hung on exit. The job was stopped and things continued with the DTSExec process still running silently in the background. I'm guessing this meant it was still enlisting the DTC. Killing the process didn't help. However, restarting the MSDTC service worked a treat. HTH :)

Karthik Jakkula said...

I had the same issue. Had a sequence container(Transaction set to required) with a Delete SQL Task, Data flow task and a script task. Setting the ValidateColumnMetadata made the difference. It worked like magic. Thanks