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

db042190 1,516 Reputation points
2021-03-04T15:04:53.203+00:00

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 Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,523 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

9 additional answers

Sort by: Most helpful
  1. Michael Taylor 51,346 Reputation points
    2021-03-04T16:02:59.747+00:00

    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.


  2. Monalv-MSFT 5,896 Reputation points
    2021-03-05T03:02:27.827+00:00

    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.


  3. db042190 1,516 Reputation points
    2021-03-17T14:59:40.8+00:00

    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
    

  4. Russel Loski 421 Reputation points
    2021-03-24T14:42:45.087+00:00

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