File System vs SSIS Catalog (SSISDB) with a Complete Hands-On 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, and gives you a full end-to-end workshop you can follow step by step.

What Runs Where

Option A: SSIS Catalog (SSISDB)

You deploy an SSIS project (.ispac) into SSISDB. Executions are managed and logged by the Integration Services server and the SSISDB database.

This is the centralized management model Microsoft recommends for deployed projects, providing:

• Parameters
• Environments
• Execution history
• Error messages
• Monitoring
• Operational troubleshooting

Execution always happens on the SQL Server hosting SSISDB, even if you trigger it remotely from SSMS on a jump box.

Option B: File System (.dtsx or .ispac on a share)

You store packages on disk or a network share and run them using DTExec, a scheduled task, or an orchestrator.

This requires SSIS runtime on the machine doing the execution.

If your jump box only has SSMS installed, you are not executing the package locally. You are only triggering execution on another server such as SQL Agent or SSISDB.

Why SSISDB Is Usually Best Practice

If you’re running anything beyond quick one-off jobs, SSISDB should be your default choice.

It gives you:

• Central deployment model for projects and packages
• Parameter and environment management with clean dev test prod separation
• Built-in execution history and detailed logging via catalog.operation_messages
• Standard monitoring workflows
• Easier troubleshooting and auditing
• Consistent execution behavior across environments

When File Share Execution Is Acceptable

File system execution is fine when:

• You have simple utility packages
• You are supporting legacy deployments
• You have a dedicated SSIS runner server
• You maintain strong operational discipline

The risk is that logging, consistency, and repeatability are on you, not the platform.

Workshop: Run the Same Package Both Ways

Scenario

Export data from SQL Server to a CSV file on a network share.

Assumptions

• SQL Server: YOURSQL01
• Database: YourDB
• Source table: dbo.ExportDemo
• Output share: \FileServer01\Exports\
• Package name: ExportTableToCsv.dtsx
• Project name: ExportProject
• SSISDB folder: OpsExports

Part 1: Testing Mode Checklist

Before you run anything, always confirm:

1. Where the package lives

• SSISDB or file system?
• SSISDB packages can be executed from SSMS
• File system packages require SSIS runtime on the execution machine

2. Execution identity

• SQL Agent job owner or proxy
• SSISDB execution context
• Permissions to database and file share

3. File share access

• Execution account must write to \FileServer01\Exports\
• Never use mapped drives
• Always use UNC paths

4. Logging and troubleshooting

• SSISDB uses built-in catalog views
• File system execution requires custom logging

Part 2: Build the Example SSIS Package

In SSDT (Visual Studio with SSIS extensions)

1. Create Integration Services Project
2. Add package ExportTableToCsv.dtsx

Parameters

• p_ConnectionString
• p_OutputFolder
• p_FileName (example: ExportDemo_YYYYMMDD.csv)

Control Flow

• Add Data Flow Task: DFT Export ExportDemo

Data Flow

• OLE DB Source

SELECT * FROM dbo.ExportDemo;

• Flat File Destination
  Path = @[User::p_OutputFolder] + @[User::p_FileName]

Flat File Connection Manager

• Delimited
• UTF-8 if needed
• Column headers optional

At this point the package is complete. Now we run it two ways.

Part 3: Best Practice Deployment Using SSISDB

Step A: Create the SSIS Catalog

In SSMS, right-click Integration Services Catalogs → Create Catalog.
This creates SSISDB and enables centralized execution and logging.

Step B: Deploy the Project

Deploy the .ispac using:

• SSMS Deployment Wizard
• Or automated deployment using catalog.deploy_project

Step C: Create Folder and Environment

USE SSISDB;
GO
EXEC catalog.create_folder @folder_name = N'OpsExports';
EXEC catalog.create_environment
    @folder_name = N'OpsExports',
    @environment_name = N'Prod'; GO
    

Step D: Create Environment Variables

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;';
GO

Step E: Map Environment Variables to Parameters

In SSMS, configure the project so:

• p_OutputFolder maps to OutputFolder
• p_ConnectionString maps to ConnStr

This mapping is the core reason SSISDB scales cleanly across environments.

Step F: Execute from SSMS or Remotely

Right-click package → Execute
Execution happens on the SQL Server hosting SSISDB.

Step G: Monitor and Troubleshoot with SQL

SELECT TOP (20)
    execution_id,
    start_time,
    end_time,
    status
FROM catalog.executions
ORDER BY execution_id DESC;

DECLARE @execution_id bigint = 123456;
SELECT message_time, message
FROM catalog.operation_messages
WHERE operation_id = @execution_id
ORDER BY message_time;

Part 4: File Share Execution (Alternative)

When it makes sense

• Dedicated SSIS runner server
• Scheduled or orchestrated execution
• Controlled versioned share for packages

Run from a file share

dtexec /F "\\FileServer01\SSIS\ExportProject\ExportTableToCsv.dtsx"

Run SSISDB package using DTExec

dtexec /ISSERVER "\SSISDB\OpsExports\ExportProject\ExportTableToCsv.dtsx" /SERVER "YOURSQL01"

Part 5: Jump Box Rule (Important)

If your jump box only has SSMS:

• You cannot run SSIS locally
• You can only trigger execution on a server with SSIS installed

Best options:

• Deploy to SSISDB and run from SSMS
• Create a SQL Agent job and trigger it remotely

Do not attempt file system execution from a jump box unless SSIS runtime is installed there.

Quick Decision Guide

Use SSISDB when you want:

• Repeatable operations
• Clean configuration
• Strong logging
• Easy troubleshooting
• Production reliability

Use file system execution when you:

• Have a dedicated runner box
• Need lightweight utility execution
• Accept manual logging and discipline

References

Microsoft Learn: SSIS Catalog overview
Microsoft Learn: Integration Services server and catalog
Microsoft Learn: Deploy SSIS projects and packages
Microsoft Learn: Run SSIS packages
Microsoft Learn: dtexec utility
Microsoft Learn: Run SSIS from command line
Microsoft Learn: Deploy SSIS project with SSMS
Microsoft Learn: catalog.operation_messages view
Microsoft Learn: SSIS logging guidance
Microsoft Learn: Monitor running packages and operations
Microsoft Learn: catalog.deploy_project stored procedure
Microsoft Learn: SSIS projects and solutions