SSIS Incremental load with SQL Server Change Data Capture (CDC)

[@azeezullasheriff7746]

How to enable CDC to capture realtime data. The demo was fantastic. Appreciate your thoughts to bring this demo

[@BiInsightsInc]

Please see my latest video on Kafka real-time ETL. It does cdc with real-time data. Also, I have a video on services broker that does real time CDC between sql servers.

[@chuckybang]

Great video, thank you sir

[@bedsepankaj]

Great demo, thanks. What's the stg table, you are truncating in 2nd package ?

[@BiInsightsInc]

Thanks Pankaj. That's the staging table used for the updates (stg_cdc_employees) after CDC Splitter.

[@user-lz6fh6kv5e]

How to dynamically move multiple incremental record (insert update delete) sql tables from one server to another using SSIS?

[@BiInsightsInc]

You can create data flows for each table and provide thier respective config details there. Or you can go for a package for each table, more maintable approach, and move data between servers.

[@jomkuy]

Can this cdc ssis job run automatically after the source got new data/data changed ?

[@BiInsightsInc]

The end product is a SSIS package and you can schedule via sql server agent or execute via script that can fire on database change. So if you have experience with triggers then yes you can automate it. Hope this helps.

[@user-zy8we2kp3i]

can we do CDC for views? Or only for tables ?

[@BiInsightsInc]

It's only for tables. It digs into the table insert and update operations therefor, it's appilcable to tables.

[@arthursanches9215]

On CDC Splitter you made an InsertOutput and an UpdateOutput case, what about the DeleteOutput ones?

[@BiInsightsInc]

I didn't see the need as most folks won't delete from a warehouse. In any case, you can find the delete case in following article. Sorry for the delayed reply. www.mssqltips.com/sqlservertip/5815/sql-server-integration-services-ssis-cdc-tasks-for-incremental-data-loading/

[@chinhansc]

How to enable CDC on SQL Container DB?

[@BiInsightsInc]

I presume you meant SQL Server running in a docker container? Then the process to enable CDC should be similar.

[@nickostrife]

What is the purpose of destination table? I tried to do insert, update, delete on the source table, but instead replicating it, destination table just keep capturing all of the modified rows (including delete and update) as a new row?

[@BiInsightsInc]

Destination table in this example is used as your data warehouse table. You can load changed data from captured instance table to this destination. I have shown you an example where update, in the source, causes update to the same row in destination table, not as a new row. Same rows are updated in the destination. Only Insert in the source will be a new row in the destination. Please refer to below blog for more details, it includes deletes. www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/

[@quanghoangminh7626]

I have to do some Data Tranformation task to create some Dimension/Fact Tables from Source file. Where should I put the Data Transformation part? Initial Load or Incremental Load?

[@BiInsightsInc]

Hi Quang, if you are performing data transformation tasks such as handling null etc then the transformation logic would go in the both Initial and incremental load. However, if you are only creating dimensions and fact tables then you can put them in the initial part.

[@quanghoangminh7626]

@@BiInsightsInc Ok thank you! You save me from a headache. May I ask can I use both SCD (Slowly changing dimension) and CDC at the same time?

[@BiInsightsInc]

@@quanghoangminh7626 yes you can use both at the same time.

[@drummermike5150]

Hi, what is the best way to handle this with multiple tables? Using a ForEach Loop Container?

[@BiInsightsInc]

I am not sure if you can use ForEach Loop container with different with varying schema and data types. You can use dimension load pattern where you can define a package for each dimension and perform all your transformation there. Once you are done with your dimension tables then load your fact tables.

[@drummermike5150]

@@BiInsightsInc Thank you! I ended up doing this: kzbin.info/www/bejne/l5qoc3mabpylo7c&lc=UgwGQQoFYxcUCoRvFxN4AaABAg.9gbrmcyZBHX9gceTJLIAyn

[@SeanAstro]

When I try to run the SSIS task "CDC Control Task" with the operation "Get Processing Range" to begin an incremental load of data, I get the "There is no change data for this table" error. I have a different package for the initial date load as you have uses the "Mark initial load start" that works fine. Thanks for any guidance!

[@BiInsightsInc]

There can be various factors but try the following; 1) check if there are changes to source table, 2) check if shadow table has changed rows. 3) are your saving cdc state to a table? If so use the provided script to query changed rows via script. Hope this helps.

[@SeanAstro]

@@BiInsightsInc I think it turned out to be a permissions issue ;)

[@BiInsightsInc]

@@SeanAstro I'm glad it's sorted out.

[@natashachowdhury418]

Hi..how do I save the CDC state of multiple tables in the same table?

[@BiInsightsInc]

Hi thanks for stopping by. You can select the same table under "Table to use for storing state". However, it needs additional configurations. You'd need to add the table name column to the CDC state table and need to filter for the table you're performing the ETL load. Hope this helps.

[@drummermike5150]

@@BiInsightsInc Could you elaborate on this a little bit. I created the standard CDC_States table and added a column for table name but I don't see any way for the CDC Control Task Editor to write to that column.

[@bebongbui3754]

I'm try to do like you however when I execute query select @to_lsn, @from_lsn it returns 0x000001EB000042AA001F and 0x000001E10000257C010E but SELECT * FROM [cdc].[fn_cdc_get_net_changes_dbo_W_PROFIT_CENTER_D] (@from_lsn, @to_lsn, 'all'); It returns false and it shows the error message is Msg 313, Level 16, State 3, Line 5 An insufficient number of arguments were supplied for the procedure or function . How I can fix it?

[@BiInsightsInc]

The number of parameters are good. Make sure you are setting @from_lsn, @end_lsn properly. Run through it again with below checks; Also, check out this article on cdc.fn_cdc_get_all_changes: malapatidatabase.wordpress.com/2017/08/22/how-to-analyze-and-read-change-data-capture-cdc-records/ ---Here is a test scenario DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10); SELECT @begin_time = GETDATE()-1, @end_time = GETDATE(); SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time); SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_TAT_TIME(@begin_lsn,@end_lsn,'all') SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_TAT_TIME(@begin_lsn, @end_lsn, 'all');

[@krunaljagani5941]

Hello Sir, Is there any way if we are using SQL server business intelligence instance ? I am not able to perform this operation.

[@BiInsightsInc]

Check your sql server version. It is not supported on all versions. Change Data Capture is supported in the Developer and Enterprise Editions of SQL Server up to and including SQL Server 2016 RTM. SQL Server from 2016 with Service Pack 1 and higher also supports CDC in Standard Edition. It is unsupported in web and express edition.

[@krunaljagani5941]

@@BiInsightsInc Thank you for your reply. We have SQL server 2012 Business Intelligence edition. So I guess, its not supported. But is there any work around or different technique(By using SSIS or Incremental load or change tracking) to implement this functionality in unsupported versions ?

[@BiInsightsInc]

@@krunaljagani5941 sure you can use incremental load approaches in SSIS. Here is a video that covers different approaches of incremental load in SSIS: kzbin.info/www/bejne/Y3zFaKiHiadoe80 Article on SSIS Incremental Load with Datetime Columns: radacad.com/ssis-incremental-load-with-datetime-columns