question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked Monalv-msft edited

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

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.

sql-server-transact-sqlsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i'm tagging this question with sql server t-sql tag as well because the t-sql based solution proposed here seems inconsistent and i'm pretty certain wont be compatible with indexes that have computed columns.

0 Votes 0 ·
RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered

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/).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

A script component in SSIS generally won't be doing any DB work (that's what the other components are for) so it isn't part of a transaction directly AFAIK. However it can use the existing connection managers to do data work. In that case it depends on what work you're doing. In my experience you should use a script component to set up variables or adjust the datasets in use but not do any DB updates. Use the standard data components to do the actual DB work later. For example you might need to import a table into your DB but one of the columns needs to be adjusted and a script component is the only way to do that (for whatever reason). So the script component (via a foreach or something) would get the current row's data, probably update a different column with the final value to use. After the script runs on all the rows in the data then you move on to one of the standard data components to actually update the database with the adjusted values. Hence the script itself doesn't do any transactional work.

The reason I'd go this route is that the data components support setting up workflows if an error occurs. This is harder to do in a script component especially if the script is making explicit calls to the DB as you would then need to worry about transactions.

If you really, really still want to do DB updates in your script then this discussion talks about setting up the transaction in the data flow and then getting access to the transaction within the script when getting the connection manager that will be used.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thx cooldadtx , its a sql script component as mentioned in the original post, better known as "execute sql task". sorry about that.

I think you are thinking of c# and vb script components in your answer, aka "scrip tasks".

0 Votes 0 ·

Yes I was, sorry about that.

0 Votes 0 ·

Set the TransactionOption to Required to force a transaction. If you just want your SQL script task to run in a transaction then set it at that level otherwise set it at the container or package level depending upon your needs.

The IsolationLevel determines the type of locking to use and maps to the standard DB isolation levels. Choose the one that is required for your transaction.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @db042190-2983 ,

We can use ROLLBACK TRANSACTION; in Execute SQL Task.

But what if the update statement fails? In that case, the SSIS package will fail because an error occurred and it will stop running. Any remaining open connections will be closed when the SSIS package is shut down. The SQL Server database engine will roll back the transaction when the connection is closed. If you prefer to have more control over when the transaction is rolled back, another Execute SQL Task can be added to the control flow. This task uses a Failure precedence constraint (the red arrow). This task explicitly rolls back the transaction with the following statement:
ROLLBACK TRANSACTION;

Please refer to SQL Server Integration Services Transactions without MSDTC.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thx so cooldadtx's transactionoption=required wont cause the pkg to fail if the update statements fail? and doesnt that option require that a certain service called msdtc (or something like that ) be running? i dont think all IT depts i've worked in have that running.

and with the roll back i suppose the catch block where the rollback occurs could raise an error to bring down the pkg. i think if the severity level is high enough , thats how it works. we would definitely want the pkg to fail.

0 Votes 0 ·

Hi @db042190-2983 ,

May I know if you use the ROLLBACK TRANSACTION; in Execute SQL Task?

Please refer to Integration Services Transactions and SSIS Script task enlist in the current transaction.

Best regards,
Mona



0 Votes 0 ·

hi monalv, i'm leaning toward doing it in t-sql. i was a little surprised that xact-abort might be necessary. i have to re read that link from russel and get my head around it. i was always under the impression that what is between a start tran and end tran is an "all or none" proposition. now i'm not so sure.

0 Votes 0 ·
Show more comments

hi monalv. are you able to get erland sommerkog's attention on the t-sql based solution? i'm going to see if an additional tag can be attached to this post. he might respond to sql engine questions.

0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered db042190-2983 commented

i couldn't fit this in a reply so i'm posting it in an "answer". I wonder what russel and the community think about this.

based on what russel's link says , i think this is what i want. but as you can see i'm not sure. and the severity of the raiseerror worries me. the code you see updates toward the end of the package the high keys from this run so next run will only look at new rows on the tables you see. presently only the update thru final insert are in the pkg exec sql component. i added the rest in an effort to get my head around russel's solution. the question marks are where passed params from ssis are used.

   declare @dt datetime2(7) = getdate()
   SET XACT_ABORT ON
      
   begin try
   begin transaction
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database1'
   update whjobcontrol set enddate =@dt where enddate is null and packagename='LoadX' and databasename='Database2'
                                                                           --high key,start date, enddate
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table1',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table2',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table3',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table4',?,@dt,null
    
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table5',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table6',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table7',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database1','dbo','Table8',?,@dt,null
    
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','Table9',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableA',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableB',?,@dt,null
   insert whjobcontrol select 'LoadX',null,null,'Database2','dbo','TableC',?,@dt,null
      
   COMMIT TRANSACTION  --seems odd to place it here 
   end try
    
   begin catch
   --ROLLBACK   doesnt seem necessary based on definition of xact_abort and otherwise i'd block begin and end tran differently
   -- i want this as severe as possible so pkg will go down too, my recollection is at least level 20
   --   and i hope the account running our pkgs is in the sysadmin role.
   --   I'm worried testers wont be in that role so how will they test this
   RAISERROR ('LoadX failed to reset job control',20,1)
   end catch
      
   SET XACT_ABORT OFF
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think that an error severity greater than 10 should cause the execute SQL task to fail and to raise an error that will cause the package to fail by default.

The COMMIT TRANSACTION is right where it belongs.

Do you have 12 different parameters? Or are the parameters all the same value? If so, I would assign the parameter ? to a variable and use that variable throughout your script.

Also, you can replace your insert statements using the Values key word.

insert whjobcontrol
values ('LoadX',null,null,'Database1','dbo','Table1',?,@dt,null),
('LoadX',null,null,'Database1','dbo','Table2',?,@dt,null),
('LoadX',null,null,'Database1','dbo','Table3',?,@dt,null),
('LoadX',null,null,'Database1','dbo','Table4',?,@dt,null)

0 Votes 0 ·

thx russel. 12 different values each being the high key for that table encountered this run.

so rusel, the rollback is not necessary as your link implied?

and does the community know for sure what starting severity level brings down a pkg? i may start looking around for that answer.

0 Votes 0 ·

i should mention before we use this as a guide. If i'm not mistaken , if indexes with computed values are even just updated as a byproduct of a script, and xact_abort is on, the script will fail. can the community comment? i may need to look for a more generic solution. we dont have such indexes in this case but we use indexes a lot in other parts of the same system built (computed) on cast to bigint of the rowversion in each record.

0 Votes 0 ·

I test to verify that there is still a transaction open and then ROLLBACK.

IF @@Trancount > 0
ROLLBACK

0 Votes 0 ·
Show more comments
RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered db042190-2983 commented

@db042190-2983 : I can't get the reply to work

Call me paranoid. I don't like commit transaction or rollback transaction without first testing. I put the code segment before every commit and rollback.

 IF @@TRANCOUNT > 0
   BEGIN
     PRINT 'Committing transaction'
     COMMIT TRANSACTION
   END
    
 -- After the Begin Catch
    
 IF @@TRANCOUNT > 0
   BEGIN
     PRINT 'Rollback transaction'
     ROLLBACK TRANSACTION
   END

If "SET XACT_ABORT ON" causes the transaction to abort, then you won't see Rollback transaction in the messages.

What I use "SET XACT_ABORT ON" for are cases where within a try catch and after a BEGIN TRANSACTION an error occurs and SQL doesn't go to the CATCH section. It goes to the next statement. There a number of common errors that do that. What I can confirm is that "SET XACT_ABORT ON" will cause those errors to go to the CATCH section. I don't know about the ROLLBACK. You will have to test and share with us.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thx, i think i understand at least partially.

putting the tran count check after the catch will cover the situation where an error didn't cause control to pass to the catch block.

but if one of those errors occurs where control goes to the next statement, wont the commit happen either way? ie whether the tran count check is there or not? i dont know what (if any) effect a commit would have in that scenario.

i'm trying to picture a way where neither the commit nor the statements after the errant statement get executed either. I think in the old days we just interrogated a return code.

0 Votes 0 ·

The code between BEGIN TRY and END TRY executes until there is an error. The code will begin executing the BEGIN CATCH after the error (SET XACT_ABORT ON increase the number of errors that will end the TRY section of the code). So if there is an error, the commit will not be run.

0 Votes 0 ·

thx Russel. I'm trying to explain that we wont be using xact_abort for some really good reasons. Therefore the commit will be attempted anyway in the scenario where an error falls thru to the next statement(s). Unless of course some additional error code checking is added. or unless i dont understand what @@trancount brings to the table.

I'm going to post a combo of your recommendations and my concerns here shortly with comments.

0 Votes 0 ·
db042190-2983 avatar image
0 Votes"
db042190-2983 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered db042190-2983 commented

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

now that i think about it, in the happy path perhaps return 0 would be better because ssis likes that.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

db042190-2983 avatar image
0 Votes"
db042190-2983 answered db042190-2983 commented

thanks erland and russel. it looks like my bad memory was confusing arithabort and xact_abort.

the former is the one that affects reads/writes etc that involve indexes with computed columns. and i'm not even sure it can affect things in more recent versions of sql (90 and beyond) even if explicitly set off.

my apologies.






· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

oh yes, i think my lack of set based sql traces back to ssis's familiar problem with mapping variables to even slightly complex inline queries. thats easily overcome by making such queries a proc.

0 Votes 0 ·

As a matter of fact, ARITHABORT does not affect anything at all, as long as ANSI_WARNINGS is ON.

What you may have in mind is that under compat level 80, ARITHABORT had to be ON for index on computed columns be considered, and having it OFF would also prevent writing to the table. Those days are gone. (For that setting, I should hasten to add. ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING and ANSI_WARNING must still be ON. And NUMERIC_ROUNDABORT must be OFF.)

0 Votes 0 ·

thx erland

0 Votes 0 ·