The issuance of the IDENTITY column is part of the transaction so we are protected from loss of data.

In SSMS, Query Window 1, run this:

USE YourDatabaseName;

DROP TABLE dbo.StagingTable

CREATE TABLE dbo.StagingTable (StagingTableID INT NOT NULL IDENTITY(1,1), Col1 sysname);

In Query Window 2, populate the table with some data:

BEGIN TRAN

INSERT INTO dbo.StagingTable

(

Col1

)

SELECT TOP 1000

c1.name

FROM sys.columns c1

CROSS JOIN sys.columns c2;

-- note, I am leaving the transaction open on purpose

In Query Window 3, insert a single row:

INSERT INTO dbo.StagingTable

(

Col1

)

SELECT TOP 1

c1.name

FROM sys.columns c1

CROSS JOIN sys.columns c2;

-- note that the insert succeeds despite the fact

-- that there are 1000 rows trying to, but not yet

-- committed on another SPID

Query Window 4, along comes our SSIS Package wanting to get the MAX ID and copy the data:

SELECT MAX(StagingTableID)

FROM dbo.StagingTable;

Nope, must wait...due to the open transaction in Window 2. Go back to Window 2 and call COMMIT or ROLLBACK, your choice, and you'll see as soon as that happens Window 4 completes immediately and returns 1001. This is the protection we need, i.e. no lost data.