The "SSIS Server Maintenance Job" executes the internal.cleanup_server_retention_window stored procedure in the SSISDB database. The stored procedure retrieves the "Retention Period (days)" property of the SSIS Catalog, and then proceeds to delete data in the SSISDB database that is older than the number of days specified by that property.
This functionality is implemented incorrectly. Specifically, the comparison that restricts the data to be deleted involves the end_time column in the internal.operations table, which is declared as datetimeoffset(7), and a local variable named @temp_date, which is declared as datetime. Since a datetime value is treated as having time zone offset +00:00 when compared with a datetimeoffset value, and the end_time values are recorded in local time, this results in incorrect rows being deleted from tables in SSISDB, for any server that is in a time zone with non-zero offset.
For example, if the retention period is set to 1 day, and the server is located in a time zone with +12:00 offset, the maintenance job will delete any data that is older than 12 hours, causing unintended data loss.
The proposed fix is to change two lines in the stored procedure as follows, replacing commented lines with the lines that follow:
--DECLARE @temp_date datetime
DECLARE @temp_date datetimeoffset(7);
--SET @temp_date = GETDATE() - @retention_window_length
SET @temp_date = DATEADD(day, - @retention_window_length, SYSDATETIMEOFFSET());