is my ssis sql script component by default running as a transaction?

Question

hi we run 2014 enterprise. one of my sql scripts contains 2 updates and about 10 inserts.

i'm looking at its properties and 2 stand out, iso level serializable and tran option supported.

by any chance is this running as a single tran or do i need to wrap it in a tran or change the properties to accomolish that? I want it to roll back if there is a failure.

Answer 1

By default the Execute SQL task script doesn't create transaction. My suggestion is that if you need a transaction use normal SQL transaction handling (try catch, commit transaction, rollback, and set xact_abort on https://sqlskull.com/2020/02/22/sql-server-set-xact_abort/).

Answer 2

I don't think Russel is really correct here.

If you are in a TRY block and an error occurs, one of the followings can happen:

1. You connection is broken. (Because it was an internal error in the engine with severity > 20.)
2. The CATCH block is fired. This is of course the expected behaviour.
3. The procedure is exited without firing the CATCH handler. If there is an outer CATCH handler, that CATCH handler will fire. Else execution will continue on the next statement in the caller, unless XACT_ABORT is ON. The situation where this happens is when you get a compilation error at run-time, for instance a missing table.
4. There are some other weird and odd situations, which can cause the error to be suppressed.

Can execution continue on the next statement? Yes, I think so, but these are errors where you involve special features, like for instance calling the CLR. I should know, since I have written a series of articles about error handling, but there are so many weird details, I can't remember them all by heart. It is certainly not the normal case.

Anyway, I have a primer on error handling here: http://www.sommarskog.se/error_handling/Part1.html

And if you want to read it all, just continue with the other parts and appendixes. But that is a lot longer.

Short summary: best practice is to have SET XACT_ABORT ON + TRY CATCH. And yes, you need IF @@trancount > 0 ROLLBACK TRANSACTION. The transaction will not have been rolled back at this point, you need to do it explicitly.

Answer 3

I was a little short on time last night, so I did not look at your script in detail.

No, I don't believe you have good reasons to deviate from best practice and run with XACT_ABORT OFF.

I dont want to do this because even in a happy path xact_abort doesnt play well with computed columns in indexes,

What on Earth do you have in mind here? XACT_ABORT ON has nothing to do with indexed computed columns. There are situations where you may have turn off XACT_ABORT ON, for instance if you consciously perform an action you know can fail and if so you want to try plan B. But those are special cases. XACT_ABORT should always be ON to reduce the risk for orphaned transactions and other evil things.

if @@rowcount < 1 goto RBACK--<--as russel pointed out,

That check may sense after an UPDATE. If you expect the UPDATE to always hit a row, then this check can serve as an assertion. But it may also be perfectly normal that there are no rows to update. In case it is an error, I would rather raise an error to take me to the CATCH than having a GOTO.

But it is quite pointless to have this check after an INSERT statement that always will insert a single row.

Also for the inserts - you could use UNION ALL to insert all rows in a single statement.

All these things could clean up the code quite a bit. Right now it is quite difficult to see the business-logic forest for all the error-handling trees.

Answer 4

taking russel's research into account and my own concerns, here is what i think i need unless i'm missing something major.

declare @dt datetime2(7) = getdate()
--replacing ? with this var so it can be tested syntactically in ssms
declare @x tinyint=1
   --i dont want to do this because even in a happy path xact_abort doesnt play well with computed columns in indexes, 
   --  and i want a one size fits all solution
   --SET XACT_ABORT ON


   begin try
   begin transaction
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database1'
   if @@rowcount < 1 goto RBACK--<--as russel pointed out, some errors fall thru, wish i had a status code to check instead
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database2'
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table1',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table2',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table3',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table4',@x,@dt,null
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table5',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table6',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table7',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table8',@x,@dt,null
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','Table9',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableA',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableB',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableC',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   --tran count must be > 0 so not asking sql if it is 
   --seems to me control could have passed here if a certain type of error occured that doesnt pass control to the catch block
   --  and/or even some of th update/inserts might have executed after one of those types of errors
   COMMIT TRANSACTION   
   end try

   begin catch
   ROLLBACK   
   RAISERROR ('LoadX failed to reset job control',11,1)
   end catch

   --i dont want to do this for reasons shown up at the beginning of the script
   --SET XACT_ABORT OFF

--seems redundant, maybe i'm not understanding russel's research.  it sounds like control can be passed here just by falling thru or 
-- via one of the GOTOS
RBACK:
if @@trancount > 0
begin
ROLLBACK   
RAISERROR ('LoadX failed to reset job control',11,1)
end

Answer 5

thx Erland. I perused your post. I'm reading between the lines here a bit but am assuming that if you believe i have good reasons not to use xact_abort, that my GOTOs and slight redundancy are warranted. I added a couple of returns (one is a little inconsistent wit your article) and an additional check on tran count per your article.

declare @dt datetime2(7) = getdate()
--replacing ? with this var so it can be tested syntactically in ssms
declare @x tinyint=1
   --i dont want to do this because even in a happy path xact_abort doesnt play well with computed columns in indexes, 
   --  and i want a one size fits all solution
   --SET XACT_ABORT ON


   begin try
   begin transaction
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database1'
   if @@rowcount < 1 goto RBACK--<--as russel pointed out, some errors fall thru, wish i had a status code to check instead
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database2'
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table1',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table2',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table3',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table4',@x,@dt,null
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table5',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table6',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table7',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table8',@x,@dt,null
   if @@rowcount < 1 goto RBACK

   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','Table9',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableA',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableB',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableC',@x,@dt,null
   if @@rowcount < 1 goto RBACK
   --tran count must be > 0 so not asking sql if it is 
   --seems to me control could have passed here if a certain type of error occured that doesnt pass control to the catch block
   --  and/or even some of th update/inserts might have executed after one of those types of errors
   COMMIT TRANSACTION  
   return 
   end try

   begin catch

   if @@trancount > 0 ROLLBACK   
   RAISERROR ('LoadX failed to reset job control',11,1)
   return
   end catch

   --i dont want to do this for reasons shown up at the beginning of the script
   --SET XACT_ABORT OFF

--seems redundant, maybe i'm not understanding russel's research.  it sounds like control can be passed here just by falling thru or 
-- via one of the GOTOS
RBACK:
if @@trancount > 0
begin
ROLLBACK   
RAISERROR ('LoadX failed to reset job control',11,1)
end