SSIS Set Variable Transform (FREE)

⚡ Key Benefits

• No-Code Variable Assignment — Set SSIS variables from upstream column values without a Script Task
• Multiple Set Modes — Choose from 7 modes including Set, Increment, Append, and placeholder replacement for flexible variable manipulation
• Row-by-Row Accumulation — Use Append mode with custom headers, footers, and separators to build strings across all pipeline rows
• Increment & Count Support — Increment variables by a fixed number, by buffer size, or by upstream column value to count or sum rows
• NULL-Safe Operation — Configure a custom fallback value when upstream column data is NULL

📸 Visual Overview & Setup

1. Drag & Drop from Toolbox 🔍 View Full Size	2. Create SSIS Variable 🔍 View Full Size	3. Configure Transform & Set Mode 🔍 View Full Size
4. Select Input Column 🔍 View Full Size	5. Execute & Verify Variable Value 🔍 View Full Size	6. Advanced Settings UI 🔍 View Full Size

🔧 Key Features

Variable Set Modes

• Set — Assign the upstream column value directly to the variable
• SetWithReplacePlaceholders — Inject the column value into a template string using the <%value%> placeholder
• Append — Concatenate each row's column value to the variable across all pipeline rows
• AppendAndReplacePlaceholders — Append with placeholder-based template support
• Increment — Add a fixed positive or negative number to the variable each row
• IncrementByBuffer — Increment the variable by the total row count of each buffer
• IncrementByColumnValue — Add the upstream column's numeric value to the variable (e.g., running sum)

Append Mode Options

• Configurable Row Separator (supports \r\n, \t, \n)
• Separate Last Row Separator for clean list termination
• Optional Header and Footer strings around the accumulated result

Placeholder Support

• Use <%value%> to inject upstream column value into a template string
• Reference other SSIS variables inline using {{User::MyVar}} or {{System::PackageName}}

NULL Handling

• Enable UseCustomValueForNull to substitute NULL inputs with a custom string
• Defaults: NULL strings → blank, NULL numeric types → 0

Diagnostics

• Logging level: Normal, Medium, Detailed, Debugging
• Optional timestamp prefix on log messages

Important Usage Note

• Connect to a downstream component (e.g., ZS Trash Destination) or set RunInOptimizedMode=false on the Data Flow task — otherwise SSIS Engine may skip the unattached transform during optimized execution

💡 Common Use Cases

Common real-world scenarios where this transform eliminates the need for a Script Task:

• Row Counting: Increment a counter variable per row to track pipeline throughput.
  Example: Use IncrementByBuffer mode to count total rows processed.
• Building Delimited Lists: Accumulate column values into a comma-separated string.
  Example: Append customer IDs with a comma separator for use in a downstream SQL query.
• Capturing Last Row Value: Overwrite the variable each row to retain the final value from a sorted dataset.
  Example: Capture the latest timestamp from a sorted source for incremental load tracking.
• Running Totals: Sum a numeric column across all rows without a Script Task.
  Example: Use IncrementByColumnValue to accumulate a revenue column.
• Building Dynamic Strings: Inject column values into a JSON or SQL template per row.
  Example: Build a JSON array body for a downstream REST API call.

🎯 Summary

Replace Script Tasks with a simple, configurable transform that sets SSIS variables from data flow column values.
Count rows, build lists, capture last values, and inject column data into templates — all with zero code.

Trusted by Developers & IT Teams Worldwide Built for SSIS Workflows: Purpose-built for high-performance ETL and complex integration scenarios. Expert Technical Support: Direct access to engineers via email and remote screen-share sessions. Proven Enterprise Scale: Trusted by 3000+ teams across 90+ countries, including Fortune 500.

SSIS PowerPack Includes SSIS Set Variable Transform Read reviews on Capterra →