CDC

Question

First time doing CDC in SSIS, Do I need to have two separate SSIS package for Initial load and Incremental load?
help me to get started, what are steps to implement

thanks
V

Answer 1

Hi Vaishu,

You can have one package, please refer to http://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/

---

Arthur

MyBlog

Twitter

Answer 2

it is very useful link ,  I have a question,   the article explain about one table as an example,  i have about 7 tables.  My understanding from doc.  

1. I need to create 14 ssis package (7  for full load and  7 for incremental) right? 

2. 7 cdc_states tables with different name?

Can some help me to understand CDC implementation in SSIS for my requirment

Thanks

V

Answer 3

Still can be part of one package if you wish so, just need multiple pairs of everything and the load does not need to happen in a particular order then you do not need to connect the pairs which means it will run in parallel.

There is nothing wrong if you will go with multiple packages either, the solution may as a result become more maintainable.

Remember, every element must exist to ensure smooth operation not how they are broken apart.

---

Arthur

MyBlog

Twitter

Answer 4

 Let me clarify,  what you are saying is,  if i add multiple tables in same ssis, it should work right? 

incremental ssis package, i can load tables, sequentially right? 

Answer 5

The tables belong to the database, but the supporting SSIS Tasks can be in one or better several packages residing in pairs e.g. to init the CDC of each table then to do the incremental loads.

---

Arthur

MyBlog

Twitter

Answer 6

i am good on init load it is one time load,  for incremental load,  can we have one package for all tables,  7 seq. container for 7 table , executed sequentially,  In  this way, i can have one package for all my tables and one cdc_stage table.   thanks in advance for your help to understand cdc implementation 

  

Answer 7

The package will need to have to contain the CDC tasks for each table

---

Arthur

MyBlog

Twitter