SSIS Execution Best Practices: File System vs SSISDB Catalog with Complete Workshop

When testing or running SSIS packages, one of the most common points of confusion is where the package executes, where it should be stored, and why Microsoft recommends the SSIS Catalog (SSISDB) over file system execution for most production workloads. This guide walks through both models, explains why each exists, what changed in SQL Server 2025, and gives you a full end-to-end workshop you can follow step by step.

What Runs Where

Why SSISDB Is Best Practice

If you are running anything beyond quick one-off jobs, SSISDB should be your default choice. It gives you everything the file system model requires you to build yourself:

• Central deployment model for projects and packages via .ispac files
• Parameter and environment management — clean Dev, Test, and Prod separation without touching packages
• Built-in execution history and detailed logging via catalog.operation_messages and catalog.executions
• Standard monitoring workflows in SSMS or via catalog views
• Easier troubleshooting and auditing — every execution is recorded with start time, end time, status, and error messages
• Consistent execution behavior across environments via environment variables
• Version rollback — SSISDB retains previous project versions and lets you restore to an older deployment

SQL Server 2025 Changes That Affect This Decision Updated 2025

SQL Server 2025 made several changes to SSIS that reinforce the SSISDB recommendation and deprecate parts of the file system model:

When File System Execution Is Still Acceptable

File system execution is reasonable when:

• You have simple utility packages that do not need central logging
• You are supporting legacy deployments and migrating in phases
• You have a dedicated SSIS runner server with strong operational discipline
• You are running DTExec as part of an orchestrated pipeline that provides its own logging

The risk is that logging, consistency, and repeatability are entirely on you — not the platform. When something fails overnight, diagnosing it is significantly harder without the SSISDB execution history.

Side-by-Side Comparison

SSISDB Default Settings to Change After Creation

SSISDB ships with defaults that work fine for small, new environments but cause serious problems at scale. Two settings in particular should be changed immediately after creating the catalog:

-- Change SSISDB defaults immediately after catalog creation
-- Run in SSMS: right-click SSISDB catalog -> Properties, or use T-SQL:

USE SSISDB;
GO

-- Reduce max versions per project from 10 to 3
EXEC catalog.configure_catalog
    @property_name  = N'MAX_PROJECT_VERSIONS',
    @property_value = 3;

-- Reduce retention period from 365 to 90 days
EXEC catalog.configure_catalog
    @property_name  = N'RETENTION_WINDOW',
    @property_value = 90;

-- Verify current settings
SELECT property_name, property_value
FROM catalog.catalog_properties
ORDER BY property_name;

Ensure SSISDB Transaction Log Backups Are Running

SSISDB uses the Full recovery model by default. Without regular transaction log backups, the log file grows without bound. Add SSISDB to your standard log backup jobs or maintenance plan:

-- Confirm SSISDB recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'SSISDB';
-- Should show FULL

-- Add SSISDB to your existing log backup job, or create one:
BACKUP LOG SSISDB
TO DISK = N'\\backup-share\SSISDB\SSISDB_log.trn'
WITH COMPRESSION, STATS = 5;

Workshop: Run the Same Package Both Ways

Scenario: Export data from SQL Server to a CSV file on a network share, using an SSIS package that runs both ways — via SSISDB and via file system — so you can see the difference in practice.

Before running anything, confirm these four points. Most failures trace back to missing one of them.

• Where does the package live — SSISDB or file system? SSISDB packages can be triggered from SSMS. File system packages require SSIS runtime on the execution machine.
• What is the execution identity? SQL Agent job owner or proxy, or SSISDB execution context? Does that account have database and file share permissions?
• Does the execution account have write access to \\FileServer01\Exports\? Never use mapped drives — always use UNC paths.
• How will you troubleshoot a failure? SSISDB uses built-in catalog views. File system requires custom logging you build yourself.

In SSDT (Visual Studio with SSIS extensions), create the package that both deployment methods will use:

USE SSISDB;
GO
EXEC catalog.configure_catalog @property_name = N'MAX_PROJECT_VERSIONS', @property_value = 3;
EXEC catalog.configure_catalog @property_name = N'RETENTION_WINDOW',     @property_value = 90;

-- Automated deployment via T-SQL
DECLARE @ispac VARBINARY(MAX);
SELECT @ispac = BulkColumn
FROM OPENROWSET(BULK N'C:\Builds\ExportProject.ispac', SINGLE_BLOB) AS ispac;

EXEC catalog.deploy_project
    @folder_name   = N'OpsExports',
    @project_name  = N'ExportProject',
    @project_stream = @ispac;

USE SSISDB;
GO

-- Create the folder
EXEC catalog.create_folder
    @folder_name = N'OpsExports';

-- Create a Prod environment
EXEC catalog.create_environment
    @folder_name       = N'OpsExports',
    @environment_name  = N'Prod';

USE SSISDB;
GO

EXEC catalog.create_environment_variable
    @folder_name       = N'OpsExports',
    @environment_name  = N'Prod',
    @variable_name     = N'OutputFolder',
    @data_type         = N'String',
    @sensitive         = 0,
    @value             = N'\\FileServer01\Exports\';

EXEC catalog.create_environment_variable
    @folder_name       = N'OpsExports',
    @environment_name  = N'Prod',
    @variable_name     = N'ConnStr',
    @data_type         = N'String',
    @sensitive         = 0,
    @value             = N'Data Source=YOURSQL01;Initial Catalog=YourDB;Integrated Security=SSPI;';

USE SSISDB;
GO

-- Get the environment reference ID first
DECLARE @ref_id BIGINT;
EXEC catalog.create_environment_reference
    @folder_name       = N'OpsExports',
    @project_name      = N'ExportProject',
    @environment_name  = N'Prod',
    @reference_type    = R,
    @reference_id      = @ref_id OUTPUT;

-- Map OutputFolder variable to p_OutputFolder parameter
EXEC catalog.set_object_parameter_value
    @object_type       = 20,  -- 20 = project parameter
    @folder_name       = N'OpsExports',
    @project_name      = N'ExportProject',
    @parameter_name    = N'p_OutputFolder',
    @parameter_value   = N'OutputFolder',
    @value_type        = R;   -- R = reference to environment variable

-- Recent executions with status
SELECT TOP 20
    e.execution_id,
    e.folder_name,
    e.project_name,
    e.package_name,
    e.status,           -- 1=Created, 2=Running, 3=Cancelled, 4=Failed, 5=Pending, 6=Ended Unexpectedly, 7=Success, 8=Stopped, 9=Completed
    e.start_time,
    e.end_time
FROM catalog.executions e
ORDER BY e.execution_id DESC;

-- Error messages for a specific execution
DECLARE @execution_id BIGINT = 123456;  -- Replace with actual execution_id
SELECT
    message_time,
    message_type,
    message_source_name,
    message
FROM catalog.operation_messages
WHERE operation_id = @execution_id
ORDER BY message_time;

Use this when you have a dedicated SSIS runner server with the SSIS runtime installed, or for controlled legacy deployments.

Run Directly from the File System

-- Run a .dtsx file directly using DTExec
-- Must run on a server with SSIS runtime installed
dtexec /F "\\FileServer01\SSIS\ExportProject\ExportTableToCsv.dtsx"

Run an SSISDB Package Using DTExec

-- Run an SSISDB-deployed package from the command line
dtexec /ISSERVER "\SSISDB\OpsExports\ExportProject\ExportTableToCsv.dtsx" /SERVER "YOURSQL01"

The Jump Box Rule

This trips up DBAs regularly. If your jump box (the workstation or remote desktop you use to connect) only has SSMS installed:

• You cannot run SSIS packages locally on that jump box
• You can trigger execution on a server that has SSIS installed
• Execution always happens where the SSIS runtime lives — not where you clicked

Best options when working from a jump box:

• Deploy to SSISDB and execute from SSMS — execution runs on the SQL Server
• Create a SQL Agent job and run it remotely from SSMS — execution runs on the Agent server
• Do not attempt file system package execution from a jump box unless SSIS runtime is installed on that machine

Quick Decision Guide

References

• Microsoft Docs – SSIS Catalog Overview
• Microsoft Docs – Deploy SSIS Projects and Packages
• Microsoft Docs – Run SSIS Packages
• Microsoft Docs – DTExec Utility
• Microsoft Docs – catalog.deploy_project
• Microsoft Docs – catalog.operation_messages view
• Microsoft Docs – Monitor Running Packages and Operations
• MSSQLTips – What’s New in SSIS 2025
• MSSQLTips – SSIS Catalog Best Practices