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

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.

Answer 3

Hi @db042190 ,

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.

Answer 4

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

Answer 5

@db042190 : 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.