Example of handling a late-arriving customer as part of an SSIS Partial Cache Lookup Transform

Late-Arriving-Customer.sql

DECLARE @CustomerID INT
SELECT @CustomerID = ?

IF NOT EXISTS (SELECT *
	FROM   WideWorldImportsDW.Dimension.Customer
	WHERE  [WWI Customer ID] = @CustomerID)
INSERT WideWorldImportersDW.Dimension.Customer
	([WWI Customer ID],
	Customer,
	[Bill To Customer],
	Category,
	[Buying Group],
	[Primary Contact],
	[Postal Code],
	[Valid From],
	[Valid To],
	[Lineage Key])
VALUES ( @CustomerID, -- WWI Customer ID - int
	N'Unknown:'           + Cast(@CustomerID AS NVARCHAR(10)),-- Customer - nvarchar(100)
	N'',-- Bill To Customer - nvarchar(100)
	N'',-- Category - nvarchar(50)
	N'',-- Buying Group - nvarchar(50)
	N'',-- Primary Contact - nvarchar(50)
	N'',-- Postal Code - nvarchar(10)
	Sysdatetime(),-- Valid From - datetime2(7)
	'9999-12-31 23:59:59.9999999',-- Valid To - datetime2(7)
	2 -- Lineage Key - int
	)