SQL Server Integration Services (SSIS) Transformations Tutorial

Types of Transformations

SSIS provides many built-in transformations. Some of the most commonly used include:

• Derived Column: Create new columns or modify existing ones using expressions.
• Lookup: Join data from another source based on matching keys.
• Aggregate: Perform calculations like sum, average, count on groups of rows.
• Conditional Split: Route rows to different outputs based on conditions.
• Sort: Order data rows by specified columns.

Derived Column Transformation

The Derived Column transformation allows you to add new columns or update existing columns by applying expressions. This is useful for data cleansing or creating calculated fields.

• Supports string, numeric, date, and logical expressions.
• Can replace existing columns or add new ones.
• Commonly used for data formatting and calculations.

Lookup Transformation

Lookup transformation performs a join between input data and a reference dataset, typically from a database table. It is used to enrich data by adding related information.

• Supports full cache, partial cache, and no cache modes.
• Can redirect rows with no matching lookup to error outputs.
• Useful for data validation and enrichment.

Aggregate Transformation

Aggregate transformation groups rows and performs calculations like sum, count, average, min, and max on grouped data.

• Supports grouping by one or more columns.
• Useful for summarizing data before loading.
• Can improve performance by reducing data volume.

Conditional Split Transformation

Conditional Split routes rows to different outputs based on specified conditions, similar to an IF-ELSE logic.

• Enables branching logic in data flows.
• Can filter or separate data for different processing paths.
• Conditions are defined using expressions.

Step 1: Add Derived Column

Create a Derived Column transformation to add a 'CustomerStatus' column with this expression: `PurchaseAmount > 1000 ? "Premium" : "Standard"`.

Step 2: Add Conditional Split

Use Conditional Split to route rows where 'CustomerStatus' equals 'Premium' to one output and others to a different output.