CODEWITHSUNDEEP
sqlt-sqlssisoledblookup
Vidya Nambiar
Vidya Nambiar

Reputation: 23

SSIS Incremental Load-15 mins

I have 2 tables. The source table being from a linked server and destination table being from the other server. I want my data load to happen in the following manner:

  1. Everyday at night I have scheduled a job to do a full dump i.e. truncate the table and load all the data from the source to the destination.
  2. Every 15 minutes to do incremental load as data gets ingested into the source on second basis. I need to replicate the same on the destination too.

For incremental load as of now I have created scripts which are stored in a stored procedure but for future purposes we would like to implement SSIS for this case.

The scripts run in the below manner: I have an Inserted_Date column, on the basis of this column I take the max of that column and delete all the rows that are greater than or equal to the Max(Inserted_Date) and insert all the similar values from the source to the destination. This job runs evert 15 minutes.

How to implement similar scenario in SSIS?

I have worked on SSIS using the lookup and conditional split using ID columns, but these tables I am working with have a lot of rows so lookup takes up a lot of the time and this is not the right solution to be implemented for my scenario.

Is there any way I can get Max(Inserted_Date) logic into SSIS solution too. My end goal is to remove the approach using scripts and replicate the same approach using SSIS.

Upvotes: 0

Views: 546

Answers (1)

KeithL
KeithL

Reputation: 5594

Here is the general Control Flow:

enter image description here

There's plenty to go on here, but you may need to learn how to set variables from an Execute SQL and so on.

Upvotes: 1

Related Questions