In SQL Server, a transaction is a sequential set of actions (such statements or queries) carried out as a single task. Data in a database may be read, written, updated, or deleted throughout these activities. Transactions that adhere to the ACID characteristics guarantee data integrity.

• Atomicity: Ensures that a transaction is treated as a single unit. Either all operations in the transaction are completed successfully, or none are applied, maintaining the "all-or-nothing" principle.
• Consistency: Guarantees that a transaction transforms the database from one valid state to another, adhering to all defined rules, constraints, and relationships.
• Isolation: Ensures that concurrent transactions do not interfere with each other, maintaining data integrity as if transactions were executed sequentially.
• Durability: Ensures that once a transaction is committed, its changes are permanently recorded in the database, even in the event of a system failure.

Transaction Control
The following are the commands used to control transactions.

• BEGIN TRANSACTION: Marks the start of a transaction. All subsequent operations will be part of this transaction until it is committed or rolled back.
• COMMIT: Saves all the changes made during the transaction permanently to the database. Once committed, the changes cannot be undone.
• ROLLBACK: Reverts all changes made during the transaction to their state at the start of the transaction, effectively canceling the transaction.
• SAVEPOINT: Creates a checkpoint within a transaction. This allows rolling back a transaction to a specific point without undoing the entire transaction.
• RELEASE SAVEPOINT: Deletes a previously defined SAVEPOINT. Once released, SAVEPOINT can no longer be used for rollback.
• SET TRANSACTION: Configures a transaction with specific properties, such as setting it to read-only or read/write, or associating it with a specific rollback segment.

Types of Transactions

• Implicit Transactions
  
  • Automatically initiated by the database system when specific commands (e.g., INSERT, DELETE, UPDATE) are executed.
  • The transaction remains active until explicitly committed or rolled back by the user.
• Explicit Transactions
  
  • Manually initiated and controlled by the user.
  • Typically defined using BEGIN TRANSACTION, followed by COMMIT or ROLLBACK to either save or undo changes.
• Autocommit Transactions
  • The default transaction mode in most SQL systems.
  • Each individual SQL statement is automatically committed if it executes successfully. No explicit commands are needed to commit or rollback.
• Savepoints
  • Checkpoints within a transaction that allow partial rollbacks.
  • Useful for rolling back a specific part of a transaction without undoing the entire

Basic Transaction Syntax
Explicit Transaction Example
BEGIN TRANSACTION;

-- Deduct from one account
UPDATE EmpSalary_int
SET Salary = Salary - 100
WHERE EmpID = 1;

-- Add to another account
UPDATE EmpSalary_int
SET Salary = Salary + 100
WHERE EmpID = 2;

-- Commit the transaction
COMMIT;

Using ROLLBACK
BEGIN TRANSACTION;

UPDATE products
SET stock_quantity = stock_quantity - 10
WHERE product_id = 5;

-- Simulating an error
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction failed and was rolled back.';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction completed successfully.';
END

Failed transaction

Savepoints for Partial Rollbacks
BEGIN TRANSACTION;

-- Step 1
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 1, GETDATE());

SAVE TRANSACTION SavePoint1;

-- Step 2
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (101, 2, 5);

-- Rollback to SavePoint1 if needed
ROLLBACK TRANSACTION SavePoint1;

-- Commit remaining operations
COMMIT;

Here, we can see in the second table that order_details data is not saved because we have set rollback savepoint1.

TRY...CATCH Example
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE EmpSalary_int
    SET Salary = Salary - 100
    WHERE EmpID = 1;

    UPDATE EmpSalary_int
    SET Salary = Salary + 'null'
    WHERE EmpID= 2;

    COMMIT;
    PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'An error occurred. Transaction rolled back.';
END CATCH;

Failed transaction

Conclusion
In SQL, transactions are sequences of operations performed as a single logical unit of work, ensuring data consistency and integrity. A transaction follows the ACID properties: Atomicity (all-or-nothing execution), Consistency (ensures data validity), Isolation (independence of concurrent transactions), and Durability (changes persist after completion). Transactions are crucial for managing database operations reliably and are typically controlled with commands like BEGIN, COMMIT, and ROLLBACK.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, we will learn how to create derived columns. Derived column task in SSIS is used to,

• Create new column
• Update existing columns
• Merge different columns into one column. For example, businesses want to concatenate first name, middle name, and last name to make it a full name.

Now let’s understand this with an example. We will use the database, which has already been loaded into the SQL server, to create a derived column function. Now let’s go to SSMS and see the table how it looks right now.

So here let’s say, the business wants to merge the first name, middle name, and last name of the customers in one column, so to perform this operation we need to use the derived column task. Now let’s go on the Data Flow Task in SSIS and perform the below steps.

Step 1. Create OLE DB Source connection – we will add OLE DB Source and Derived Column Task in the Data Flow Task and establish a source connection. We can see this in the screenshot below.

Step 2. Derived Column Transformation –To create new column values, we perform derived column transformation by applying expressions. We will go on Derived Column Editor to add derived column names and specify expressions to create new column values.

Step 3. Now click Ok and Create an OLE DB Destination connection – We will establish a new OLE DB Destination connection in the editor to push new data. So here will give the connection manager name and the new table name as CustomerNameDerivedCol.

Step 4. Now hit ok and go to Mappings to see available input columns and available destination columns, here you can notice that the same column name is mapped.

Step 5. Now the destination connection is established and the SSIS package is executed successfully.

Step 6. Now let’s verify this in SQL Server Management Studio (SSMS). In the below screenshot at the end of the result, we can see the CustomerName column. That’s how we derive a new column.

Summary
In this article, you have learned how to create the derived column in SSIS, hope you liked it.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.