Any Alternative Change Data Capture in SSIS from an azure SQL database

Question

Any Alternative Change Data Capture in SSIS from an azure SQL database

RTamagnini 40

Since SQL Azure below S3 does not allow using CDC in SSIS, is there any alternative?

if Sql Azure allows the activation of CDC, even creating tables/views and functions related to CDC , is there any way to use it in SSIS?

Thanks in advance

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2024-01-22T18:48:56.5366667+00:00

@RTamagnini Just checking in to see if the below answer provided by @Alberto Morillo helped? If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Regards

Geetha

1 answer

Your answer

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2024-01-22T18:48:56.5366667+00:00

@RTamagnini Just checking in to see if the below answer provided by @Alberto Morillo helped? If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Regards

Geetha

Answer 1

Alberto Morillo 34,661 MVP Volunteer Moderator

You can track your LSN windows and write yourself the CDC apply logic. SSIS has the CDC extract and apply functionality built-in, and you can run SSIS CDC data flows either on your on-premises SQL Server, or in an Azure Data Factory SSIS runtime.

You can also use timestamps and triggers to achieve the same. On the comments I gave you an example.

Another option is to try Striim on Azure Marketplace which offers some CDC functionality.

RTamagnini 40 Reputation points

2024-01-04T14:17:21.8933333+00:00

can you give me a example what can i do in SSIS ?
because when I try to capture the changes it says it can't connect to Azure

Alberto Morillo 34,661 MVP Volunteer Moderator

-- Assuming you have a control table like this:
CREATE TABLE etl_control (
    last_sync_timestamp DATETIME
);

-- In your SSIS package, you would:
-- 1. Retrieve the last sync timestamp
DECLARE @last_sync_timestamp DATETIME;
SELECT @last_sync_timestamp = last_sync_timestamp FROM etl_control;

-- 2. Use the timestamp to get changes from the source table
SELECT * FROM source_table
WHERE last_modified_timestamp > @last_sync_timestamp;

-- 3. Apply changes to the destination table
-- (Inserts, updates, or deletes as needed)

-- 4. Update the control table with the new sync timestamp
DECLARE @current_timestamp DATETIME;
SET @current_timestamp = GETDATE();
UPDATE etl_control SET last_sync_timestamp = @current_timestamp;

Share via

Any Alternative Change Data Capture in SSIS from an azure SQL database

1 answer

Your answer