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

Question

Post reply

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

david_h_edmonds

Default port

Points: 1430
More actions
September 13, 2021 at 12:01 pm

#3929078

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

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

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

Phil Parkin SSC Guru Points: 246980 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

david_h_edmonds Default port Points: 1430 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246980 More actions · Answer 3

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

david_h_edmonds Default port Points: 1430 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

david_h_edmonds Default port Points: 1430 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)