SSIS Excecute SQL Task not executing simple T-SQL correctly.

  • brettwalker42

    Valued Member

    Points: 65


    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,


  • Phil Parkin

    SSC Guru

    Points: 243998

    How are you consuming/viewing the results of the query in SSIS?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply