Post reply

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

  • January 24, 2020 at 8:44 pm

    #3720290

    Hello,

    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,

    Brett

  • January 24, 2020 at 9:03 pm

    #3720304

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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