How to load updated data from source DB when source tables don't contain any updated date field for comparison in SSIS?

Question

How to load updated data from source DB when source tables don't contain any updated date field for comparison in SSIS?

hamb 26

Hi,

I am loading data from PROD DB (can't add any field in PROD) and the tables don't have any field which could compare with current date to load the updated records on daily basis in SSIS.

I have added a derived column "Date_Loaded" (with GETDate) and add a date_loaded column in the target database tables and loaded all the data from the sources but now I am stuck, how to load the updated PROD data for the next day.

Thanks.

Yitzhak Khabinsky 26,586 Reputation points

2022-05-27T18:30:23.483+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
hamb 26 Reputation points

2022-05-27T20:12:11.703+00:00

CREATE TABLE [dbo].[table1](
[i_id] [int] NOT NULL,
[i_num] varchar NOT NULL,
[eff_date] datetime NOT NULL,
[exp_date] datetime NOT NULL,
) ON [PRIMARY]

INSERT INTO dbo.table1
VALUES (1, B001AAA, '2000-06-25','2012-02-01');
INSERT INTO dbo.table1
VALUES (2, C001AAA, '2000-06-25','2012-02-01');

SQL Server Version 2017. I have mentioned the logic in my asked question above.

The tables in PROD have the same format like above, there is no relationship defined between most of the tables (some have PKs but no FKs). I have to load updated data on daily basis from PROD to staging DB using SSIS. Mostly tables have more than 1 million records.

Let me know if any other detail is needed. Thanks.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-05-27T20:44:12.987+00:00

So what is the ultimate purpose of the operation? Are you copying data from one table to another with the same schema? Or are there any transformations?
hamb 26 Reputation points

2022-05-27T20:49:04.513+00:00

Yes copying the same table's data to another DB and added a column Date_Loaded in staging target DB's table (GetDate()) and no other transformation required here.

3 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-05-27T18:30:23.483+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
hamb 26 Reputation points

2022-05-27T20:12:11.703+00:00

CREATE TABLE [dbo].[table1](
[i_id] [int] NOT NULL,
[i_num] varchar NOT NULL,
[eff_date] datetime NOT NULL,
[exp_date] datetime NOT NULL,
) ON [PRIMARY]

INSERT INTO dbo.table1
VALUES (1, B001AAA, '2000-06-25','2012-02-01');
INSERT INTO dbo.table1
VALUES (2, C001AAA, '2000-06-25','2012-02-01');

SQL Server Version 2017. I have mentioned the logic in my asked question above.

The tables in PROD have the same format like above, there is no relationship defined between most of the tables (some have PKs but no FKs). I have to load updated data on daily basis from PROD to staging DB using SSIS. Mostly tables have more than 1 million records.

Let me know if any other detail is needed. Thanks.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-05-27T20:44:12.987+00:00

So what is the ultimate purpose of the operation? Are you copying data from one table to another with the same schema? Or are there any transformations?
hamb 26 Reputation points

2022-05-27T20:49:04.513+00:00

Yes copying the same table's data to another DB and added a column Date_Loaded in staging target DB's table (GetDate()) and no other transformation required here.

Answer 1

You say that you cannot add columns in Prod, but maybe you can enable Change Tracking or Change Data Capture to track which rows that have been updated.

Else you need to compare column by column. This can be a bit laboursome when there are nullable columns involved. But here is a trick to find all changed rows:

SELECT ...
FROM  src
FULL JOUN trg ON src.pk = trg.pk
WHERE NOT EXISTS (SELECT src.* INTERSECT SELECT trg.*)

This works because for the set operators INTERSECT and EXCEPT, NULL counts like any other value.

This answer is not particularly detailed of the very simple reason we know very little about your system, so I have to be generic.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

Yes copying the same table's data to another DB and added a column Date_Loaded in staging target DB's table (GetDate()) and no other transformation required here.

So yet an option would be Transactional Replication. Then you can add extra columns in the subscriber as you see fit.

Answer 3

ZoeHui-MSFT 41,491

Hi @hamb ,

Have you consider to use Lookup Transformation to compare the data between the two tables and insert the new value to your destination table?

an-overview-of-the-lookup-transformation-in-ssis

You may also refer to ssis-dataflow-copy-only-changed-and-new-records to see if it could give you some ideas.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

ZoeHui-MSFT 41,491 Reputation points

2022-06-10T09:17:43.837+00:00

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Share via

How to load updated data from source DB when source tables don't contain any updated date field for comparison in SSIS?

3 answers

Your answer