Configure ODS using Changed Data Capture

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Configure ODS using Changed Data Capture

Author

Topic

rka
Starting Member

6 Posts

Posted - 2012-03-15 : 07:03:41

My task is to implement an ODS solution using SQL Server 2008
I have got the following environment:
Source: SAP
Target (ODS): SQL Server 2008
ETL Tool: SSIS 2008

What is the best way to implement a CDC which that gets data from SAP and updates SQL 2008 ODS target?

Note that all I can do is to read data from the source (SAP) and can not make any changes to it

Any help will be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 16:12:10

you can create a staging table at sql server and then you can make use of a lookup transformation in SSIS to identify new vs modified ones and then based on match no match output you can configure OLEDB command /oledb destination tasks to do update/insert. I hope you've audit columns in SAP to capture delta data during each run.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rka
Starting Member

6 Posts

Posted - 2012-03-15 : 19:23:46

Thanks Visakh,
That will work for any Updates and Inserts. How can Deletes be handled? Otherwise I will end up with records in ODS which has been removed in Transactional DB.

Also, I would have liked to query DMV's to track the history of changes on the record. (still Type2 will be implemented in the actual DW), but business wants some sort of reporting on the history from Operational Data as well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 22:46:31

quote:
Originally posted by rka

Thanks Visakh,
That will work for any Updates and Inserts. How can Deletes be handled? Otherwise I will end up with records in ODS which has been removed in Transactional DB.

Also, I would have liked to query DMV's to track the history of changes on the record. (still Type2 will be implemented in the actual DW), but business wants some sort of reporting on the history from Operational Data as well

For deletes you need to have an extra lookup step which looks up destination against delta and in no match you do delete from destination.
sorry if you implement Type 2 in DW, what additional reporting you need to use DMV for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources