I have an SSIS package with an Execute SQL Task that runs the following query:
SELECT startDate = ISNULL (MAX (c.FileDate), DATEADD (dd, -8, GETDATE ()))
, endDate = GETDATE ()
, fileDate = CAST(FORMAT (GETDATE (), 'yyyyMMddHHmmss') AS VARCHAR(16))
FROM stage.MI_ChildSupportFile c
WHERE RecordType = 3
AND RecordSent = 1;
There is no data in the MI_ChildSupportFile table so "DATEADD (dd, -8, GETDATE()" is the code being run for startDate.
When I run it in SSMS it works correctly.
However when I run the package, endDate and fileDate are correct but startDate is incorrect. startDate is usually a few minutes to a few hours less than endDate.
I've checked for stray variables, parameters, hard-coded values, etc. but have found no reason for startDate to be incorrect when ran in SSIS.
I'm running Microsoft SQL Server 2014 - 12.0.2000.8 (X64) and Visual Studio 2019 (version 16.4.3).
I've spent about 8 hours on this trying everything I can think of (using COALESCE instead of ISNULL, using startDate = (SELECT...), etc.) but nothing I change makes any difference.
I've reached the conclusion that this may be an SSIS bug.
Has everyone ever seen anything like this? I'm at my wits end and am not sure what to try next. I've Googled this but to no avail.
Any suggestions/advice appreciated,