You are creating a product dimension table and a SQL Server Integration Services (SSIS) package that will load the table. You need to keep a history in the table of changes to the names of products…

Adding a Slowly Changing Dimension transformation enables SSIS to detect changes in dimension attributes and route rows to the appropriate output for inserting historical records, which is the core mechanism for implementing SCD Type 2 in a package.

Change Data Capture captures row-level changes in SQL Server source tables for downstream consumption but does not itself implement the historical versioning logic needed in a dimension table.

An inferred member column is used to handle dimension members that are referenced by fact records before the dimension record has been fully loaded, not for tracking historical name changes.

Type 3 SCD stores only the current value and one previous value in separate columns of the same row, which does not preserve a full history of all name changes over time.

An end date column is required by SCD Type 2 to mark when a historical version of a product record became inactive, allowing queries to identify which record was current during any given time period.

A start date column is required by SCD Type 2 to record when each version of a product record became effective, forming the lower bound of the validity interval alongside the end date.