Execute sql task behave differently when run in isolation versus part of package

  • SQL Server 2019 Azure.

    I have a basic fact load package that someone has tagged an execute sql object onto the end of that updates various fields in a fact table.

    when I run the whole package, 2 out of the 6 columns do not update and when selecting from the table, they appear as null.

    If I execute only the sql task on its own, it updates 3 rows.

    In all my years of SSIS, I have never seen anything like this happen.

    Can anyone think what may be causing it?

    I appreciate its hard without seeing the package however the fact it works in isolation should be enough to prove the SQL is sound.

    There are also no conditions on whether the object executes or not and anyway 4 out of 6 columns do update so it is obviously executing.

    All ideas gratefully received.

     

    Dave

  • Are you 100% certain that exactly the same SQL is being executed in each case?

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hi Phil,

    Yeah its identical, I am simply executing the execute SQL task by right clicking on it in VS.

    That works whereas running the package in its entirety causes the error described.

  • david_h_edmonds wrote:

    Hi Phil,

    Yeah its identical, I am simply executing the execute SQL task by right clicking on it in VS.

    That works whereas running the package in its entirety causes the error described.

    I'd run Profiler or create an Extended Events session to verify that the same SQL is executing. Also to check that there is nothing else executing in the package which touches the table.

    The alternative explanation (that T-SQL UPDATE has a weird bug)  is incredibly difficult to countenance.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I agree it’s hard to imagine a bug with update. I will try profiler and see what falls out the other end.

    cheers as always,

    Dave

  • It could be a settings difference between VS  and SSIS.  For example, VS could be using connections that use one type of setting for ANSI NULLs and SSIS could be using the opposite.  There are other settings, as well, that could affect other behaviors as well.

    Check the connection settings for VS vs SSIS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi chaps sorry it took a few days to reply.

    the issue was that VS 2019 pro was showing the green tick on the insert task and then moving onto the update task but the insert task was actually still happening. I moved the update task later in the process and it resolved all the issues.

    Not sure if it’s due to working over VPN or routing issues or what but glad it was an easy fix.

    thanks all,

    Dave

  • Thanks for the feedback, Dave.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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