This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(16, 25%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJ%3C/text%3E%3C/svg%3E)
I am exploring Azure Data Factory CDC feature, and I am trying to perform the CDC from one SQL table to another SQL table, also the SQL table in the source has a primary key.
Whenever a new row is added to the source table, it gets added to the destination SQL table.
However, when an existing row is modified/updated in the source table, it adds a new row in the destination SQL table, instead of updating it.
Is there a way to achieve update and delete with Azure Data Factory CDC?
In the answer section of this question (https://stackoverflow.com/questions/75233423/how-are-changes-count-calculated-in-azure-data-factory-change-data-capture), the author says to specify a Primary Key, however in the current ADF CDC UI, I am unable to see the Keys column.
Please let me know if the update and delete is possible using Azure Data Factory CDC.
If your SQL source doesn't have SQL Server CDC with net_changed enabled or doesn't have any time-based incremental columns, then maybe the columns the tables in your source will be unavailable for selection.
@Amira Bedhiafi
I added the idnex_name and supports_net_changes =1, but still no luck.
Inserts work, however, update and delete doesnt.
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DE_City',
@role_name = null,
@filegroup_name = null,
@index_name='PK_DE_City',
@supports_net_changes =1,
@capture_instance = N'dbo_DE_City'
GO
Have you updated or deleted something ?
@Amira Bedhiafi
Of course.
Still, I am not seeing the Keys columns in the grid that I can use to designate a column as a Primary Key.
Has the UI of ADF changed recently?
Have you tried to switch to another browser?
@Amira Bedhiafi
Yes, I tried in Chrome and Edge and the Keys column is not visible in both.
If you cannot fix your issue, try to open a ticket to the Azure Support team.https://azure.microsoft.com/en-us/support/create-ticket/
@Amira Bedhiafi
Created the ticket:
Thanks for all your help.
Please don't forget to check the solution to your issue once you hear from the support team :)
Not yet, raised a ticket with Azure and awaiting their reply.
Let's wait and see :)
Hi jigsm,
Thank you for posting query in Microsoft Q&A Platform.
Kindly check my video on CDC and fellow same steps. That helps to avoid this error. Below is the video link. CDC (change data capture) Resource in Azure Data Factory
Hope this helps. Please let me know how it goes.
Please consider hitting Accept Answer button. Accepted answers help community as well.
Thank you for sharing the video link.
I need to implement the same way as mentioned in the video.
However, I am unable to find the Keys column in the grid to designate a column as a key.
Is it removed lately or am I missing something?
My source and sink is Azure SQL Managed Instance, maybe it is not supported?
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'DE_Delimiter',
@role_name = null,
@filegroup_name = null,
@index_name=null,
@supports_net_changes =1,
@capture_instance = N'dbo_DE_Delimiter'
GO
Hi jigsm,
Is your destination table has Key column as primary key? If not try same. Also some times it might be UI issue, you can try reopen browser widows.
For some reason, if its not working, then Would you like to check CDC option in mappoing data flows?
CDC(change data capture) for SQL Source in Mapping data flows in Azure Data Factory or Synapse
Hope this helps. Please let me know how it goes. Thank you.
Hi jigsm,
Just checking if you get chance to share updates? If your issue resolved by any other resolution steps, then kindly feel free to share resolution details here, that helps to community as well. Thank you.
@ShaikMaheer-MSFT
Not yet, raised a ticket with Azure and awaiting their reply.