SQL Server SSIS Explained: Catalog, File System Deployment, Connections, and Always On

Introduction

Data integration has always been central to SQL Server. Before SQL Server Integration Services (SSIS), Microsoft shipped Data Transformation Services (DTS) in SQL Server 7.0 (1998) and SQL Server 2000. DTS allowed developers to build workflows for importing and exporting data, but it had major limitations:

• Minimal error handling and logging
• Limited set of transformations
• Debugging was difficult
• Packages were often fragile at scale

When SQL Server 2005 launched, DTS was replaced with SSIS. This new engine introduced:

• A robust pipeline architecture with parallel execution
• Dozens of built-in transformations (Lookup, Merge Join, Derived Column, etc.)
• A script task/component for C# or VB.NET
• A modern development environment (BIDS, later SSDT in Visual Studio)
• Variables, expressions, and logging options

The most significant change came with SQL Server 2012: Microsoft introduced the project deployment model and the SSIS Catalog (SSISDB), which revolutionized how packages were deployed, parameterized, and monitored.

This guide covers everything you need:

1. History and deployment models (File System, MSDB, SSISDB)
2. How the SSIS Catalog works internally
3. Connection managers and parameterization options
4. Monitoring, reports, and prerequisites
5. A workshop on building and deploying packages
6. A workshop on configuring SSIS with Always On for high availability
7. Visual diagrams to tie it all together

---

Deployment and Storage Options

File System

Packages are saved as .dtsx files. You run them via DTExec or SQL Agent jobs.

Run example:

dtexec /f "C:\SSIS\MyPackage.dtsx" /set \Package.Variables[User::MyVar].Value;TestValue

Pros: Simple, easy to copy and version control.
Cons: No centralized logging or versioning.

---

MSDB (Legacy Package Store)

Packages are stored in the MSDB system database (sysssispackages).

Query packages:

SELECT name, versionmajor, versionminor, versionbuild
FROM msdb.dbo.sysssispackages;

Run from MSDB:

dtexec /SQL "\MyFolder\MyPackage" /SERVER MySQLServer

Pros: Central storage, backed up with SQL.
Cons: Limited logging, no versioning.

---

SSIS Catalog (SSISDB)

The modern standard since SQL Server 2012. Projects are deployed as .ispac files into SSISDB.

Key features:

• Centralized storage in the SSISDB database
• Project parameters and environments
• Built-in execution logging and performance stats
• Automatic versioning and rollback
• Security and encryption of sensitive values

Create Catalog:
In SSMS, right-click Integration Services Catalogs → Create Catalog, enable CLR, and set a password.

Check with T-SQL:

SELECT name FROM sys.databases WHERE name = 'SSISDB';

Inside the SSIS Catalog

The catalog organizes packages into:

• Folders → logical containers
• Projects → bundles of packages and project-level connections
• Packages → the .dtsx workflows
• Environments → variables (Dev/QA/Prod) bound to parameters

Useful Catalog Views

• catalog.folders – list folders
• catalog.projects – deployed projects
• catalog.packages – packages inside projects
• catalog.operations – operations (deploy, run, validate)
• catalog.executions – execution logs

Example: Recent executions

SELECT e.execution_id, e.folder_name, e.project_name, p.package_name,
       e.status, e.start_time, e.end_time
FROM catalog.executions e
JOIN catalog.packages p
  ON e.package_id = p.package_id
ORDER BY e.start_time DESC;

Connection Managers

Scope

• Package-level – visible in one .dtsx only
• Project-level – shared across all packages in the project

Built-in Types

• OLE DB – SQL Server, Oracle
• ADO.NET – .NET providers
• ODBC – generic drivers
• Flat File – delimited/fixed-width text
• Excel – workbooks
• FTP/HTTP/SMTP – integration tasks
• WMI/MSMQ – system tasks
• SSAS (OLAP) – Analysis Services
• Custom/Third-party – Oracle, SAP, Teradata

Parameterizing Connections

Define a project parameter and bind it to the connection string.

@[Project::DbConnString]

Bind to environment variable in SSISDB:

EXEC catalog.set_environment_variable_value
    @folder_name = 'ETL',
    @environment_name = 'ProdEnv',
    @variable_name = 'DbConnString',
    @value = 'Server=ProdSQL;Database=DW;Integrated Security=SSPI;';

Monitoring and Reviewing Reports

Built-in Reports in SSMS

• All Executions – history of package runs
• All Operations – deployments and validations
• Execution Performance – row counts, task timings
• Execution Messages – warnings, errors, info

Query errors with T-SQL:

SELECT em.execution_id, em.message_source_name, em.message
FROM catalog.event_messages em
WHERE em.event_name = 'OnError'
ORDER BY em.message_time DESC;

Monitoring and Maintenance Best Practices

• Set RETENTION_WINDOW to 14–30 days
• Limit max project versions (e.g., 5)
• Back up SSISDB and the encryption master key
• Monitor the SSIS Server Maintenance Job
• Avoid TRUSTWORTHY = ON for SSISDB

---

Prerequisites for SSISDB

• SQL Server 2012+ with SSIS installed
• CLR integration enabled:

sp_configure 'clr enabled', 1;
RECONFIGURE;

• SSISDB created and encrypted
• SQL Agent service account permissions

---

Workshop 1: Build and Deploy a Package

1. Create a project in SSDT
2. Data Flow Task:
   • Flat File Source → Derived Column → OLE DB Destination
3. Create staging table:

CREATE TABLE dbo.Customers_Stage (
    CustomerID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    FullName NVARCHAR(100)
);

4. Deploy to SSISDB

5. Create an Environment and add variable DbConnString

6. Execute package with bound parameter

7. Query execution logs:

SELECT * FROM catalog.executions ORDER BY start_time DESC;

Workshop 2: Configure SSIS with Always On

Step 1: Prerequisites

• SQL Server Enterprise/Developer Edition
• Availability Group already configured
• SSIS installed on all nodes

Step 2: Add SSISDB to the Availability Group

ALTER DATABASE SSISDB SET HADR AVAILABILITY GROUP = MyAG;

Step 3: Configure Jobs

• Use the AG listener name (not the node name) for SQL Agent job steps that execute SSIS packages.
• This ensures jobs continue to run regardless of which replica is primary.

Example job step (T-SQL):

EXEC msdb.dbo.sp_start_job @job_name = 'Run SSIS Package',
                           @server_name = 'MyAGListener';

🔎 Listener Clarification:

• SSISDB is just a user database under the hood. When added to an AG, it behaves like any other AG database.
• To connect to SSISDB for deployments, executions, or catalog queries, you should always use the AG listener name.
• That way, if a failover happens:
  • SSIS deployments and executions always hit the active primary replica.
  • You don’t need to reconfigure jobs, packages, or references.

Should You Use a Separate Listener?

• Same listener: Usually fine for most shops and the simplest approach.
• Separate listener: Only makes sense if you want to isolate SSIS workload, have special routing/DNS rules, or are running a very large environment.
• 👉 For most environments: one listener for all databases in the AG, including SSISDB, is recommended.

Step 4: Failover Test

• Manually fail over the Availability Group.
• Execute a package from SSISDB using the AG listener.
• Confirm that execution history continues seamlessly on the new primary.

Visuals

SSISDB Hierarchy

“Structure of the SSIS Catalog (SSISDB): folders contain projects, which hold packages, parameters, and can be linked to environments.”

SSIS Package Execution Flow

“Execution flow of an SSIS package deployed to SSISDB: from deployment, to catalog creation, to execution via ISServerExec, with logging and reporting.”

---

Summary

• DTS → SSIS → SSIS Catalog shows Microsoft’s ETL evolution.
• Deployment options: File System, MSDB, SSISDB (best practice today).
• SSISDB adds logging, versioning, security, and monitoring.
• Connection managers support dozens of sources and can be parameterized for multi-environment use.
• Built-in reports and catalog views make monitoring straightforward.
• Prerequisites: CLR enabled, SSIS installed, SSISDB created, encryption backed up.
• With Always On, SSISDB can run in a highly available environment.
• Two workshops show you how to deploy and execute packages, and how to configure HA with AGs.

---

Final Thoughts

SSIS remains a powerful, battle-tested ETL tool. While Azure Data Factory and Synapse pipelines are popular in the cloud, SSIS is still critical for on-premises and hybrid environments.

If you’re still running packages from the file system or MSDB, migrating to SSISDB will dramatically improve monitoring, troubleshooting, and operational confidence.

---

References

• Microsoft Docs – SSIS Catalog
• Microsoft Docs – Deploy Projects and Packages
• Radacad – SSIS Catalog Execution
• Tim Mitchell – Project Connections in SSIS
• SQL Solutions Group – Managing SSISDB Growth
• MSSQLTips – What is SSIS?