Understanding SSIS Architecture: Storage, Scheduling, and
Execution
The Three Critical Locations You Need to Understand
When working with SSIS packages, there are three distinct
concepts that customers often confuse. Understanding where each component
lives will help you know exactly where to install SSIS and COZYROC SSIS+.
1. Where Packages Are STORED (The Package Repository)
This is simply where your deployed SSIS packages are saved
for later use. Think of this as a "filing cabinet" for your packages.
Storage Options:
SSIS
Catalog (SSISDB) - A database on a SQL Server instance that stores
deployed SSIS projects (SQL Server 2012 and later)
MSDB
Database - Older storage method where packages are saved to SQL
Server's MSDB system database
File
System - Packages stored as .dtsx files in folders (e.g., C:\SSIS\Packages\)
SSIS
Package Store - A managed folder location (default: C:\Program
Files\Microsoft SQL Server\[version]\DTS\Packages)
Key Point: The server that stores your
packages does NOT need to be the same server that executes them.
2. Where Packages Are SCHEDULED FROM (The Job Scheduler)
This is where you create scheduled jobs that tell the system
when to run your packages.
Scheduling Options:
SQL
Server Agent - The most common scheduler, installed with SQL Server
(except Express editions)
Windows
Task Scheduler - Can call dtexec.exe to run packages
Azure
Data Factory - For cloud-based scheduling
Third-party
schedulers - Any tool that can execute dtexec.exe or call SSIS APIs
Key Point: SQL Server Agent runs as a Windows service
on a specific SQL Server instance. The SQL Server Agent job can reference
packages stored on a different server than where the Agent resides.
3. Where Packages Are EXECUTED (The Runtime Engine)
This is the server that actually runs the package code—where
the SSIS runtime engine processes your data transformations, reads sources,
writes to destinations, etc.
Execution Locations:
The
local machine where SQL Server Agent resides (most common scenario)
A
remote SQL Server with SSIS installed (when using SSIS Catalog
deployment)
Azure-SSIS
Integration Runtime (for cloud execution)
Any
machine with SSIS installed that can be called via dtexec.exe
Key Point: SSIS must be installed on EVERY machine
where packages actually execute. This is the execution server or runtime
server.
Common Deployment Scenarios
Let me break down the most common scenarios so you can see
how these three concepts work together:
Scenario 1: Everything on One Server (Simple On-Premises
Setup)
|
Component
|
Location
|
|
SSIS Catalog (SSISDB)
|
Server A
|
|
SQL Server Agent
|
Server A
|
|
Package Execution
|
Server A
|
What needs to be installed on Server A:
SQL
Server Database Engine
Integration
Services (SSIS feature)
COZYROC
SSIS+ (with license)
How it works: You deploy packages to the SSIS Catalog
on Server A, create a SQL Server Agent job on Server A, and when the job runs,
it executes the package locally on Server A.
Scenario 2: Centralized Catalog with Remote Execution
(Enterprise Setup)
|
Component
|
Location
|
|
SSIS Catalog (SSISDB)
|
Server A (Central Catalog Server)
|
|
SQL Server Agent
|
Server B (Execution Server)
|
|
Package Execution
|
Server B (Execution Server)
|
What needs to be installed:
Server A (Storage only):
SQL
Server Database Engine
Integration
Services (SSIS feature) - technically optional if only storing
packages, but recommendedyoutube
COZYROC
SSIS+ - NOT required (packages are only stored here, not executed)
Server B (Execution):
SQL
Server Database Engine
Integration
Services (SSIS feature) - REQUIRED
SQL
Server Agent service
COZYROC
SSIS+ (with license) - REQUIRED
How it works: You deploy packages to the SSIS Catalog
on Server A. On Server B, you create a SQL Server Agent job and configure the
job step to point to the SSIS Catalog on Server A (by specifying Server A's
name in the "Server" field). When the job runs, SQL Server Agent on
Server B tells Server A's SSIS Catalog which package to run, but the actual
execution happens on Server B using Server B's SSIS runtime engine.
Scenario 3: Multiple Execution Servers with One Catalog
(Load Distribution)
|
Component
|
Location
|
|
SSIS Catalog (SSISDB)
|
Server A (Central Catalog Server)
|
|
SQL Server Agent #1
|
Server B (Execution Server 1)
|
|
SQL Server Agent #2
|
Server C (Execution Server 2)
|
|
Package Execution #1
|
Server B
|
|
Package Execution #2
|
Server C
|
What needs to be installed:
Server A:
SQL
Server Database Engine
Integration
Services (SSIS feature)
COZYROC
SSIS+ - NOT required
Server B and Server C:
SQL
Server Database Engine
Integration
Services (SSIS feature) - REQUIRED
SQL
Server Agent service
COZYROC
SSIS+ (with license) - REQUIRED on BOTH servers
How it works: Packages are stored once on Server A.
Server B and Server C each have their own SQL Server Agent jobs that point to
packages in Server A's catalog, but each server executes its assigned packages
locally using its own SSIS runtime.
Scenario 4: File System Storage with Local Execution
|
Component
|
Location
|
|
Package Storage
|
File system folder (e.g., \\FileServer\SSIS\ or C:\SSIS\)
|
|
SQL Server Agent
|
Server A
|
|
Package Execution
|
Server A
|
What needs to be installed:
Server A:
SQL
Server Database Engine
Integration
Services (SSIS feature) - REQUIRED
SQL
Server Agent service
COZYROC
SSIS+ (with license) - REQUIRED
How it works: The .dtsx package files are stored on a
file share or local folder. SQL Server Agent on Server A has a job step
configured with "File System" as the package source. When the job
runs, Server A's SSIS runtime loads the .dtsx file and executes it.
Where to Install SSIS and COZYROC SSIS+
Now that you understand the scenarios, here's the simple
rule:
Install SSIS on:
Every
server where packages will execute (the runtime server)
Optionally
on the server that hosts the SSIS Catalog (recommended for management, but
not strictly required if only storing packages)
Install COZYROC SSIS+ on:
License COZYROC SSIS+ on:
Every
execution server (production, QA, UAT, shared development
environments) where scheduled packages run
Development
machines do NOT require a license
Installation Checklist by Environment Type
Development Machines (Package Design)
✅ Required:
❌ NOT Required:
SQL
Server Database Engine (optional, but helpful for testing)
Integration
Services (optional, SSIS Projects Extension provides design-time
components)
COZYROC
SSIS+ License
Execution Servers (Where Packages Run)
✅ Required:
SQL
Server with Integration Services installed
COZYROC
SSIS+ Components Suite
COZYROC
SSIS+ License (tied to the SQL Server version you're targeting)
SQL
Server Agent service (if scheduling packages locally)
SQL Server Edition Requirements:
Production/QA/UAT:
Standard, Enterprise, or Web Edition (all require paid licenses)
Development/Testing:
Developer Edition (free, but non-production use only)
NOT
Supported: Express Edition (does not include SQL Server Agent or
Integration Services)
SSIS Catalog Servers (Package Storage Only)
If you have a dedicated server that ONLY stores packages in
SSISDB and does NOT execute them:
✅ Required:
❌ NOT Required:
Verification Steps
To verify Integration Services is installed:
Open
SQL Server Configuration Manager
Look
for SQL Server Integration Services [version] in the list of
services
Or
check if this folder exists: C:\Program Files\Microsoft SQL
Server\[version]\DTS\Binn\
To verify COZYROC SSIS+ is installed on an execution
server:
Check
if this folder exists: C:\Program Files (x86)\CozyRoc\SSIS\
Open
Visual Studio → Check if COZYROC components appear in the SSIS
Toolbox
For
execution servers, verify the license is applied by running the COZYROC
License Manager application
To verify your SQL Server Agent can access a package:
In SQL
Server Management Studio, expand SQL Server Agent → Jobs
Create
a test job with a step type of SQL Server Integration Services Package
Set
the Package source to match where your packages are stored (SSIS
Catalog, File System, etc.)
If
using SSIS Catalog on a remote server, specify the remote server name in
the Server field
Common Mistakes to Avoid
❌ "I installed SQL
Server, so I have SSIS"
❌ "I installed COZYROC on
my development machine, so production will work"
❌ "The SSIS Catalog is on
Server A, so COZYROC must be installed there"
❌ "SQL Server Agent and
SSIS Catalog must be on the same machine"
❌ "I licensed COZYROC for
SQL Server 2016, so it will work when I upgrade to SQL Server 2019"
Quick Decision Matrix
Use this table to determine what to install:
|
Machine
Purpose
|
SQL
Server
|
Integration
Services
|
SQL
Agent
|
COZYROC
SSIS+
|
COZYROC
License
|
|
Development Workstation
|
Optional
|
Optional (Extension provides design-time)
|
No
|
Yes
|
No
|
|
SSIS Catalog Server (storage only)
|
Yes
|
Yes (recommended)
|
No
|
No
|
No
|
|
Execution Server (local packages)
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
|
Execution Server (remote catalog)
|
Yes
|
Yes
|
Yes
|
Yes
|
Yes
|
|
Scheduler Server (no local execution)
|
Yes
|
Optional
|
Yes
|
No
|
No
|
Summary: The Golden Rule
Install SSIS and COZYROC SSIS+ on the machine where
packages actually RUN, not just where they are stored or scheduled from.
Storage
(SSIS Catalog) = Just a database holding package definitions
Scheduling
(SQL Server Agent) = Just a job scheduler that triggers execution
Execution
(SSIS Runtime Engine) = Where the actual work happens → This is where
SSIS and COZYROC must be installed
If you're unsure which machine executes your packages, look
at your SQL Server Agent job step configuration, the "Server" field
(for SSIS Catalog packages) or the "Run as" proxy account tells you
which machine's SSIS runtime will be used.