Ssis-586 English 【Instant Download】

/* 2b. Insert the new version */ INSERT INTO dbo.DimCustomer (CustomerID, FirstName, LastName, Email, Phone, Address, City, State, ZipCode, BirthDate, StartDate, EndDate, IsCurrent) SELECT src.CustomerID, src.FirstName, src.LastName, src.Email, src.Phone, src.Address, src.City, src.State, src.ZipCode, src.BirthDate, GETDATE(), NULL, 1 FROM #Stg_Customer src JOIN ChangedRows cr ON src.CustomerID = cr.CustomerID;

| Pitfall | Symptom | Fix | |---------|---------|-----| | | Duplicate “current” rows appear. | Always include AND tgt.IsCurrent = 1 in the ON clause. | | NULL comparison breaks change detection | A column that becomes NULL never triggers an update. | Use ISNULL(col, '') (or a sentinel value) in the WHERE clause of the ChangedRows CTE. | | Large source tables cause timeouts | Package runs for >30 min and fails. | Switch to a set‑based incremental load : add a LastModifiedDate column to the source and filter the staging load ( WHERE LastModifiedDate > @LastRun ). | | Temp table scope lost between tasks | “Invalid object name '#Stg_Customer'” errors. | Keep the Execute SQL Tasks that reference #Stg_Customer inside the same connection manager with RetainSameConnection = True (set on the connection manager). | | Identity key collisions after bulk inserts | DimCustomerKey not unique. | Use the identity column; never try to insert explicit keys unless you turn IDENTITY_INSERT ON for a reason. | ssis-586 english