SSIS Execute SQL Task not executing simple T-SQL correctly

Question

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 - v12.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,

Brett

startDate is usually a few minutes to a few hours less than endDate. - is that the correct or the incorrect behaviour? — GSerg
When you say it works correctly in SSMS, what do you mean by "correctly"? What data are you expecting? What data is SSIS actually giving you back? Sample data is much more useful than descriptions of data. How many records in stage.MI_ChildSupportFile match your WHERE clause? — digital.aaron

Shiv Sidhu Shiv Sidhu · Accepted Answer · 2020-01-26T20:16:35

You did not explain how you are storing the results to the query. Is there an insert statement before the select statement?

I would check the following to narrow down on the issue:

Check the connection being used by the Execute Sql Task. Check its expressions to see if its connection string is being built at run time to make sure it is not connecting to some dev, test or other prod server where the table actually has data.

If the connection is static and is pointing to the same server as the one in your SSMS follow Step 2.

a. Check if the table in your query or table,if any, storing your output has any trigger on it which in any way touch the StartDate column. b. Check the execute sql task expressions to see that the query is not being generated dynamically or it's not wired to some variables.

If the answer is no, go to step 3.

Create a replica of the table with _temp and a replica of the table (if any) where you are storing your output and use those in your query in the execute sql task. Are you able to replicate the issue?

SSIS Execute SQL Task not executing simple T-SQL correctly

2 Answers