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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato