0

My control flow task looks like the following:

enter image description here

My data flow task looks like the following:

enter image description here

When sourcing data from a CDC enabled table I hit a bottle neck when performing row-by-row updates using the OLE DB Command. I am seeing huge performance hits and long run times for small amounts of records. I want to be able to run this at intervals of 2-3 minutes but am seeing run times of around 1hr :( I have heard of people using staging tables (OLE DB Destination) instead of the OLE DB Command on destination tables.

How are these intermediate staging tables usually designed? Are they duplicates of the lsn entries in the CDC tables or am I inserting SQL Commands into these tables to be ran in batch later in the control task? How do I apply changes from my intermediate tables to the final destination tables?

DatumPlane
  • 191
  • 1
  • 2
  • 15
  • Direct the Updates to a staging table that is a just a simple `insert`, then run the `update` using an `Execute SQL` task to run the `update` in one set-based operation. – iamdave Nov 14 '17 at 08:16

1 Answers1

1

Create two staging table(update and delete) and process the changes in batch. You can find more detail here --> https://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/

Siva
  • 422
  • 6
  • 16