SSIS

This skill covers SQL Server Integration Services (SSIS) across all supported versions (2014 through 2025). It provides deep knowledge of:

• Control flow engine: task orchestration, precedence constraints, containers, event handlers, transactions, checkpoints
• Data flow engine: buffer-based pipeline, execution trees, synchronous vs asynchronous transformations, error outputs
• SSISDB catalog: project deployment, parameterization, environments, execution logging, catalog views
• Package design: master-child patterns, naming conventions, modular architecture
• Data flow optimization: buffer sizing, lookup caching, source query tuning, destination fast load
• Connection managers: OLE DB, ADO.NET, ODBC, Flat File, Excel, Flexible File (Azure Blob/ADLS)
• Deployment: project model (.ispac) vs legacy package model, CI/CD with SSIS DevOps Tools
• Azure-SSIS Integration Runtime: lift-and-shift, custom setup, VNet integration, cost management
• Security: protection levels, parameterized credentials, SSISDB encryption, proxy accounts
• Script extensibility: Script Task (control flow) and Script Component (data flow) in C#/VB.NET
• Expression language: property expressions, Derived Column, Conditional Split, variable evaluation
• Migration planning: SSIS to Azure Data Factory, Microsoft Fabric, Airflow, dbt

When a question is version-specific, read the appropriate version reference below. When the version is unknown, provide general guidance and note where behavior differs across versions.

When to Use This Skill's General Guidance vs. a Version Reference

Use the general guidance in this file when:

• The question applies across SSIS versions (architecture, design patterns, optimization, deployment)
• The user's SSIS version is unknown
• The request involves migration planning or platform comparison
• The request is about SSISDB catalog management, CI/CD, or security best practices

Read a version reference when:

• The question involves version-specific features (e.g., Flexible File Task in 2019+, Entra ID in 2025)
• The question involves version-specific deprecations or breaking changes
• The user explicitly names a version ("SSIS 2025 package fails after upgrade")

How to Approach Tasks

1. Classify the request:

   • Troubleshooting -- Load references/diagnostics.md for common errors, performance bottlenecks, SSISDB monitoring queries, Azure-SSIS IR issues
   • Architecture / package design -- Load references/architecture.md for engine internals, buffer management, execution trees, connection managers, expression language
   • Best practices -- Load references/best-practices.md for design patterns, optimization, error handling, CI/CD, security, migration patterns
   • Performance tuning -- Load both references/architecture.md (buffer mechanics) and references/best-practices.md (optimization techniques)
   • Deployment / CI/CD -- Load references/best-practices.md for deployment models, DevOps tooling, multi-environment promotion
   • Migration -- Load references/best-practices.md for migration patterns to ADF, Fabric, Airflow, dbt

2. Identify version -- Determine which SQL Server / SSIS version the user runs. Key version gates:

   • Flexible File Task / Parquet support: 2019+
   • AutoAdjustBufferSize: 2016+
   • SSIS Scale Out: 2017+
   • ADO.NET with Microsoft.Data.SqlClient / Entra ID: 2025+
   • 32-bit deprecation: 2025
   • Attunity CDC removal: 2025 If version is unclear, ask.

3. Load context -- Read the relevant reference file for deep technical detail.

4. Analyze -- Apply SSIS-specific reasoning. Consider engine type (control flow vs data flow), transformation blocking behavior, buffer impact, deployment model, and connection manager selection.

5. Recommend -- Provide actionable guidance. Include SSISDB catalog queries for monitoring, property settings for tuning, and step-by-step instructions for deployment or configuration.

6. Verify -- Suggest validation steps (execute in SSDT with data viewers, check SSISDB execution reports, review catalog.event_messages, test in non-production environment first).

Core Architecture

Two-Engine Design

SSIS separates concerns across two distinct engines:

Package (.dtsx)
├── Control Flow Engine (Runtime Engine)
│   ├── Tasks (Execute SQL, Script, File System, etc.)
│   ├── Containers (Sequence, For Loop, Foreach Loop)
│   ├── Precedence Constraints (Success/Failure/Completion + expressions)
│   ├── Event Handlers (OnError, OnWarning, OnPreExecute, etc.)
│   └── Transactions and Checkpoints
│
└── Data Flow Engine (Pipeline Engine)
    ├── Sources (OLE DB, Flat File, Excel, Flexible File, etc.)
    ├── Transformations (Derived Column, Lookup, Sort, Merge Join, etc.)
    ├── Destinations (OLE DB, Flat File, Raw File, etc.)
    └── Buffer Manager (in-memory row buffers, execution trees)

Control flow manages what runs and in what order. Data flow manages how data moves and transforms within a Data Flow Task.

Buffer Architecture (Data Flow)

The data flow engine uses in-memory buffers for high throughput:

• Data flows through buffers (configurable via DefaultBufferMaxRows and DefaultBufferSize)
• Buffers are organized into execution trees from source (or async output) to next async transformation or destination
• Each execution tree gets its own worker thread

Transformation types by buffer behavior:

Type	Behavior	Examples	Impact
Synchronous	Reuses input buffer, row-by-row	Derived Column, Conditional Split, Data Conversion, Multicast	Best performance
Semi-blocking	Requires subset of rows, creates new buffers	Merge, Merge Join, Union All	Moderate -- new buffers
Fully blocking	Must read ALL input before ANY output	Sort, Aggregate	Worst -- entire dataset in memory

SSISDB Catalog

The SSISDB catalog (project deployment model, 2012+) provides centralized management:

SSISDB
├── Folders
│   ├── Projects (.ispac deployments)
│   │   ├── Packages
│   │   └── Parameters
│   ├── Environments
│   │   └── Environment Variables (Dev/QA/Prod values)
│   └── Environment References

Key capabilities: deployment versioning, parameterized execution, automatic logging, built-in SSMS reports, catalog views for custom monitoring.

Deployment Models

Model	Unit	Target	Configuration	Status
Project (recommended)	.ispac (all packages)	SSISDB catalog	Parameters + environments	Current
Package (legacy)	Individual .dtsx files	File system / MSDB	.dtsConfig XML files	Deprecated in 2025

Package Design

Master-Child Pattern

Use a master package to orchestrate child packages via Execute Package Task:

• Master handles sequencing, error notification, logging
• Each child is a self-contained unit (one per source system or target table)
• Pass values via project parameters or parent package variables
• Benefits: modularity, parallel development, independent testing, reusability

Naming Conventions

Element	Pattern	Example
Tasks	Type prefix + description	SQL_LoadCustomers, DFT_TransformOrders, SCR_ValidateInput
Data flow components	Type prefix + source/target	SRC_ODS_Customers, LKP_DimProduct, DST_DW_FactSales
Variables	User::v prefix	User::vFilePath, User::vRowCount
Connection managers	Environment + system	DEV_ODS_OleDb, PROD_DW_OleDb

Complexity Guidelines

• One data flow per logical operation -- avoid monolithic data flows
• Group related tasks in Sequence Containers for shared error handling or transactions
• If a package exceeds ~50 tasks, split into multiple child packages
• Annotate business rules directly in the package designer

Data Flow Optimization

Buffer Tuning

Property	Default	Guidance
DefaultBufferMaxRows	10,000	Increase for narrow rows; monitor memory
DefaultBufferSize	10 MB	Increase up to 100 MB for wide rows
AutoAdjustBufferSize	false	Set true (2016+) to auto-calculate from row count
EngineThreads	10	Increase for data flows with many execution trees

Key Optimization Rules

1. Prefer synchronous transformations -- Derived Column, Conditional Split, Data Conversion reuse buffers
2. Eliminate Sort/Aggregate in data flow -- Push to source query (ORDER BY, GROUP BY) wherever possible
3. Lookup: use full cache mode with indexed reference tables and only needed columns
4. Fast Load for destinations -- OLE DB Destination with BULK INSERT; tune MaxInsertCommitSize
5. Filter at source -- Use SQL queries with WHERE clauses, not table mode
6. Remove columns early -- Drop unnecessary columns before expensive transformations
7. Network packet size -- Increase from 4 KB to 32 KB on OLE DB connection manager for large transfers

Azure-SSIS Integration Runtime

Azure-SSIS IR is a managed cluster of Azure VMs in Azure Data Factory for running SSIS packages:

• Lift-and-shift: Run existing packages without rewriting
• SSISDB hosting: Azure SQL Database or Azure SQL Managed Instance
• Scaling: Configure node size and count (1-10+)
• Custom setup: Install drivers, components, assemblies on IR nodes
• VNet integration: Access on-premises data via VPN/ExpressRoute
• Cost management: Start/stop IR on schedule to avoid idle costs
• Cold start: ~20-30 minutes to provision/start

Limitations

• No SSIS Scale Out support in Azure-SSIS IR
• Custom setup adds to startup time
• 64-bit only (no 32-bit providers)
• Cost: multi-node IR running 24/7 is expensive vs serverless ADF activities

Version-Specific Guidance

Version	Reference	What's Version-Specific
SSIS 2019	references/versions/2019.md	Flexible File Task, Parquet/ORC/Avro support, Azure connectors
SSIS 2022	references/versions/2022.md	Maintenance release, minimal SSIS changes, VS 2022 tooling
SSIS 2025	references/versions/2025.md	Entra ID, TLS 1.3, deprecations (32-bit, legacy service, Attunity CDC)

Anti-Patterns

1. Monolithic mega-package -- A single package that extracts, transforms, and loads everything. Breaks modularity, blocks parallel development, and makes debugging painful. Use master-child pattern.

2. Sort/Aggregate in data flow on large datasets -- Fully blocking transformations hold the entire dataset in memory. Push to source query (ORDER BY / GROUP BY) or stage and use T-SQL.

3. No-cache Lookup on high-volume data flows -- Issues a query per row. Use full cache mode with indexed reference tables. Reserve no-cache for tiny or volatile reference sets.

4. Hard-coded connection strings -- Embeds server names, credentials, file paths directly in packages. Use project parameters mapped to SSISDB environment variables.

5. EncryptSensitiveWithUserKey in production -- Only the creating user can decrypt. Use DontSaveSensitive with parameters and SSISDB environment variables (ServerStorage encryption).

6. Ignoring error output configuration -- Leaving all components on "Fail component" for errors. Configure error outputs to redirect failed rows for investigation while allowing the pipeline to continue.

7. SELECT * in source queries -- Pulls unnecessary columns into buffers, wasting memory and reducing rows per buffer. Specify only needed columns.

8. Using package deployment model for new projects -- Legacy model lacks parameterization, environment management, and automatic logging. The package deployment model is deprecated in SSIS 2025. Use project deployment model.

SSIS Future and Deprecation Awareness

SSIS remains fully supported through SQL Server 2025's lifecycle, but Microsoft is signaling a transition:

• SSIS 2025 was announced on the Microsoft Fabric Blog, not the SQL Server blog
• Only one new feature in 2025 (ADO.NET with Microsoft.Data.SqlClient); the rest was deprecations and removals
• Microsoft positions Fabric as the next-generation unified analytics platform
• Invoke SSIS Package activity in Fabric (preview) provides a bridge for existing packages
• No EOL date announced; SQL Server 2022 extended support runs through January 2033

Practical guidance:

• Existing packages: no immediate action; begin planning migration on a package-by-package basis
• New projects: prefer ADF, Fabric, or open-source alternatives unless on-premises SQL Server is required
• Hybrid: use Azure-SSIS IR or Fabric's Invoke SSIS Package while building new workloads on modern platforms
• Prioritize migrating packages that use removed components (Attunity CDC, Oracle connector, Hadoop tasks)

Cross-Domain References

Technology	Reference	When
SQL Server	The database plugin's sql-server skill	SQL Server platform context, Always Encrypted, T-SQL optimization for source queries
ETL domain	overview skill	Cross-platform comparison, tool selection, ETL vs ELT decision framework
Azure Data Factory	adf skill	ADF pipeline design, Azure-SSIS IR configuration, migration target
Airflow	airflow skill	Airflow as migration target, DAG-based orchestration alternative

Reference Files

• references/architecture.md -- Buffer management, execution trees, connection managers, expression language, Script Task vs Script Component, error handling mechanics, deployment models, SSISDB structure, Azure integration
• references/best-practices.md -- Package design patterns, data flow optimization, error handling strategy, CI/CD deployment, security, performance tuning, testing, migration patterns to ADF/Fabric/Airflow/dbt
• references/diagnostics.md -- Common errors (type conversion, truncation, connection timeout, lookup failure, validation, 32/64-bit), performance bottlenecks, SSISDB monitoring queries, debugging techniques, Azure-SSIS IR troubleshooting

Diagnostic Scripts

Ready-made T-SQL against the SSISDB catalog in scripts/, numbered by investigation order. All read-only; run in SSMS on the catalog instance.

• scripts/01-recent-executions.sql -- 7-day execution summary by package with status mix
• scripts/02-failed-execution-errors.sql -- Error messages from failed executions (first error = root cause)
• scripts/03-package-duration-trend.sql -- 30-day duration trend (batch-window drift detection)
• scripts/04-longest-executables.sql -- Slowest tasks/containers inside packages (the bottleneck locator)