Transaction and Isolation Levels in SSIS Package

TRANSACTION OPTIONS IN SSIS PACKAGE:

NotSupported - Specifies that no transaction will be started for this container, and consequently, the outcome of the current transaction, if one has been started by a parent container, will not affect the data that may be altered during execution of this container. This means that changes will not roll back, even if the parent container started a transaction.

Supported - Specifies that this container will not start a transaction. However, it will participate in a transaction if the parent container started one. This is the default.

Required - Specifies that this container will cause a new transaction to be started unless the parent container already has a transaction, in which case, the parent’s transaction will be joined.

Isolation Levels in SSIS:

ReadUncommited:  Does not lock the records being read.  This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database.  This is called a dirty read because the data is inconsistent.

Chaos:  Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.

ReadCommitted:  Locks the records being read and immediately frees the lock as soon as the records have been read.  This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction.

RepeatableRead:  Locks the records being read and keeps the lock until the transaction completes.  This ensures that the data being read does not change during the transaction.

Serializable:  Locks the entire data set being read and keeps the lock until the transaction completes.  This ensures that the data and its order within the database do not change during the transaction.

Snapshot:  The data read within a transaction will never reflect changes made by other simultaneous transactions.  The transaction uses the data row versions that exist when the transaction begins.  No locks are placed on the data when it is read.

You can visit more on SQL Server Isolation Level :-

SQL Server Isolation Level with example

Refreshing a View

When create a view, SQL Server stores metadata information describing the view, its

columns, security, dependencies, and so on. Schema changes in underlying objects are not

reflected in the view’s metadata information. After applying such schema changes,

 it's a good practice to refresh the view's metadata information using the sp_refreshview stored procedure so that the changes are reflected in the view.

Example:

Create table:

USE tempdb;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;

CREATE TABLE dbo.Table1(col1 INT, col2 INT);

INSERT INTO dbo.Table1(col1, col2) VALUES(1, 2);

GO

Create View

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

Go

CREATE VIEW dbo.RefreshView

AS

SELECT * FROM dbo.Table1;

GO

select * from RefreshView

col1      col2

1          2

Add Column to the table

ALTER TABLE dbo.Table1 ADD col3 INT;

The schema change in T1 was not reflected in the view’s metadata information

select * from RefreshView

col1      col2

1          2

To refresh the view’s metadata information, run the sp_refreshview stored procedure

EXEC sp_refreshview 'dbo.RefreshView';

select * from RefreshView

col1      col2      col3

1          2          NULL

When done delete the table and view.

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL

DROP TABLE dbo.Table1;

Video link: