SSIS batch processing on DateTime

Question

I have created an SSIS package; it takes data from a table in Azure SQL and inserts and updates it into a DB2 table on an Id match. and we are running this ssis package on daily basis with the help of a job.

my requirement is I want to save the last run dateTime so that when it fetches the data from the Azure SQL source, it fetches the data present after that date. This is to avoid loading the whole table of azure SQL again daily when the job runs

Answer 1

You can add a technical column InsertionDate which gets the system date of the insertion.

Before your data flow which populate data from the source and the destination you can use Execute SQL Task

SELECT       ? = MAX(InsertionDate)
FROM          MyTable

Click on the Parameter Mapping tab, you should see the following form:

To add a parameter mapping, you must click on the Add button, since you have on specified one parameter in the SQL command then we should add only one mapping.

and then in your load you use another Execute SQL Task where you will put :

SELECT      *
FROM          MyTable
WHERE        (InsertionDate >= ?)

The ? refers to the variable you created before to get the max