Daily ETL package skips data with incremental load about sql-server-samples HOT 1 OPEN

You only need to reseed when generating data in the past, that is before the ETL cutoff time. So this limitation would only apply to the incremental data simulation scenario, not to production scenarios.
We could consider addressing ETL for incremental data simulation for the next iteration of the sample.

From: KoenVerbeeck [mailto:[email protected]]
Sent: Monday, September 19, 2016 5:42 AM
To: Microsoft/sql-server-samples [email protected]
Subject: [Microsoft/sql-server-samples] Daily ETL package skips data with incremental load (#132)

When refreshing data in the WWI sample databases, the SSIS package DailyETL can possible skip data when you're trying to incrementally load the data. (the documentation suggests to do a full load by reseeding the DWH first, but that's hardly a best practice)

For example, if I generate data for the Wide World Importers database using the stored procedure [DataLoadSimulation].[PopulateDataToCurrentDate], data is generated right until yesterday included. So if today is 19 September 2016, the last date will be the 18th of September 2016. When you run the SSIS package, the new cutoff time will be calculated as the current datetime - 5 minutes (e.g. 2016-9-19T21:50:00). So a certain point in time on the 19th of September 2016. All changes will be succesfully loaded into the DWH. When the ETL is done, the new cutoff time is written to the Integration.[ETL Cutoff] table in the DWH.

If at a later point in time I generate data again for the WWI database, data will also be generated for the 19th of September 2016. With the example of a cutoff time of 2016-9-19T21:50:00, you miss all changes from 2016-9-19T00:00:00 until 2016-9-19T21:50:00.

Instead of using a calculated cutoff time which is the same for all tables, it's maybe better to use the latest change date (e.g. LastEditedWhen) that can be found. For example:

DECLARE @NewCutfOff DATETIME = (SELECT MAX(LastEditedWhen) FROM Sales.Orders);

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fMicrosoft%2fsql-server-samples%2fissues%2f132&data=02%7c01%7cjodebrui%40microsoft.com%7c2863e89dff9148fadfed08d3e08a5a65%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636098857240669598&sdata=8pTpFiO%2bpRUqyAEm4J2UQs5hjd9m0cGFtXf2q0Paj5A%3d, or mute the threadhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fnotifications%2funsubscribe-auth%2fAMuA9u_BUYUbaX2DJJDuq5Vnc5oBzm5eks5qroMYgaJpZM4KAcOb&data=02%7c01%7cjodebrui%40microsoft.com%7c2863e89dff9148fadfed08d3e08a5a65%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636098857240669598&sdata=beoyrgccZq5pP2k3GG7nHQYDk7pV5DYF6K6aozIZKQY%3d.

from sql-server-samples.