Containerised Application Triggering SSIS on Host: Insights and Challenges

The Problem

When I containerised a Node.js application (using Podman on RHEL), I faced a challenge:
The app needed to trigger SSIS packages (.dtsx files) using dtexec. SSIS was installed on the host, but not inside the container. Why not install SSIS in the container?

• There’s no official SSIS container image for Linux.
• SSIS installation is complex and tied to SQL Server components.
• Maintaining SSIS inside a container would break portability and add overhead.

So, how do we trigger SSIS from a container without installing SSIS inside it?

My Approach

I decided to decouple the container from SSIS execution:

• The containerized app writes a JSON job file to a shared volume mounted from the host.
• A host-side Bash script (scheduled via cron) reads these JSON files, builds the dtexec command, and runs SSIS in the host environment.
• After execution, the script moves processed JSON files to a completed/ folder and logs the results.
• A .lock mechanism prevents concurrent runs across multiple servers sharing the same volume.

This approach is simple, reliable, and works with existing infrastructure.

Option Analysis

Before settling on this solution, I explored other approaches:

✅ Option 1: Install SSIS in the Container

• Pros: Everything runs inside the container.
• Cons: No official SSIS image, complex setup, dependency issues.
• Verdict: Not practical.

✅ Option 2: Trigger SSIS on Host via SSH

• Pros: Real-time execution, no polling.
• Cons: Requires SSH setup, key management, and network access.
• Verdict: Good for low-latency needs, but adds operational complexity.

✅ Option 3: REST API on Host

• Pros: Clean interface, easy to secure, real-time.
• Cons: Requires maintaining an API service.
• Verdict: Great for structured workflows, but overkill for my current needs.

✅ Option 4: Message Queue (RabbitMQ/Kafka)

• Pros: Scalable, reliable, supports retries.
• Cons: Adds infrastructure complexity.
• Verdict: Ideal for high-volume jobs, not needed for my use case.

✅ Option 5: Use Linux Namespaces (nsenter)

• Pros: Direct execution on host from container.
• Cons: Requires privileged container, security risk.
• Verdict: Interesting, but not suitable for production.

Why I Chose JSON + Bash

• ✅ Simple: No extra services or infrastructure.
• ✅ Decoupled: Container and SSIS execution are independent.
• ✅ Auditable: JSON files and logs provide a clear trail.
• ✅ Secure: No privileged containers or exposed APIs.

Is it the best solution? Probably not for every scenario.
But for my requirements—low complexity, low traffic, existing Linux SSIS setup, and minimal infra changes—it’s the right fit.

graph LR
    subgraph Current_Solution["Current Solution"]
        A1[Container App] --> A2[Shared Volume: JSON Job]
        A2 --> A3[Host Bash Script]
        A3 --> A4[SSIS Execution]
    end

This system processes fewer than 100 SSIS job triggers per month. That low volume was a major factor in my decision-making. I didn’t need the complexity of a REST API, message queue, or cloud orchestration for such a small workload. Instead, I prioritized simplicity, reliability, and minimal infrastructure changes.

Key Learnings

• Always evaluate operational complexity vs. business need.
• File-based job queues can be effective for simple workflows.
• Namespace tricks (nsenter) are powerful but come with security trade-offs.
• For scalability and observability, consider REST or MQ in the future.

Next Steps

• Add retry logic and a failed/ folder for error handling.
• Improve observability with status JSON or a lightweight dashboard.
• Explore REST API if real-time triggers become a requirement.
• Convert SSIS Packages to Python or NodeJS (Future Consideration). I need to do a detailed option analysis by looking into the current SSIS workflow before recommending a conversion solution.
• One possible solution could be migrating SSIS workflows to Python-based ETL scripts:
• Why?
  • Portability: Python runs natively in Linux containers.
  • Maintainability: Easier CI/CD integration.
  • Flexibility: Rich ecosystem (pandas, pyodbc, sqlalchemy).
  • Cloud-readiness: Works well with Airflow or Prefect.
• Challenges:
  • Requires rewriting SSIS logic (data flows, transformations).
  • Testing for parity with SSIS outputs.
• Benefits:
  • Eliminates SSIS dependency.
  • Fully containerized, easier scaling.
• How to Start:
  • Identify simple, high-value SSIS packages.
  • Prototype Python equivalents using pyodbc + pandas.
  • Validate outputs before migrating fully.

graph LR
    subgraph Alternative_REST_API["Alternative (REST API)"]
        B1[Container App] -->|HTTP POST| B2[Host API Service]
        B2 --> B3[SSIS Execution]
    end

    subgraph Alternative_Python_ETL["Alternative (Python ETL)"]
        C1[Container App] --> C2[Python ETL Script in Container]
        C2 --> C3[Database]
    end

    subgraph Alternative_NodeJS_ETL["Alternative (All in NodeJS)"]
        D1[Container App - ETL written in NodeJS within]
        D1 --> D2[Database]
    end