CDC Control Task: "Timeout expired" (when Transaction is required)

Question

To complete CDC Mark Initial Load Start and End, two CDC control tasks are added into a Sequence Container, if Transaction is not supported for the container, everything works fine. However, Transaction is required on the other hands, the "Mark Initial Load End" will fail raising "Timeout expired" error.

Based on tracing, the following statement is suspending. It might be waiting for the lock placed by the first CDC task "INSERT INTO [dbo].[cdc_states] ([name] ,[state])" that was running in another session.

exec sp_executesql N'SELECT count(*) FROM [dbo].[cdc_states] WHERE Upper([name]) = Upper(@name)',N'@name nvarchar(9)',@name=N'CDC_State'

Any solution to this, the initial requirement is to rollback all changes, including CDC , if "Load Dimension Table" or something else between these two CDC tasks failed.

Thanks,
NetDNA

Answer 1

Hello Net DNA,

I trust this is a "for your information" kind of the post only. Is that correct?

I also trust this error can be avoided if one would set the RetainSameConnection property of the source connection manager to true.

---

Arthur My Blog