SSIS vs. SQL — When to Use SSIS, When Pure T-SQL, When to Combine?

Published Aug 14, 2018 · Updated Jun 2, 2026
by

SSIS, T-SQL, or a combination of both? There is no single right answer. What there is, is a handful of decision criteria against which every concrete choice — technology and extent of use — should be measured: readability, source code management, impersonation. This article series takes these three axes and delivers a concrete argument per axis. Anyone arguing ETL tool choices without these criteria ends up in tool-loyalty debates instead of architecture decisions.

TL;DR:

  • SSIS vs. SQL is an architecture question, not tool loyalty.
  • Three deep-dive axes with dedicated cluster articles: Readability/Maintainability, Source Code Management, Impersonation.
  • Pragmatic synthesis: SSIS as an orchestration wrapper, T-SQL substance in Stored Procedures.
  • 2026 positioning: Microsoft Fabric Data Factory, dbt, and Postgres-native primitives shift the answer; the question remains.

Prerequisites: Working familiarity with SQL Server 2017+ and SSIS 2017+ (Visual Studio with SSDT).

Contents

Overview

SQL Server Integration Services (SSIS) is a powerful graphical toolset belonging to the Microsoft BI Stack alongside SQL Server. It supports a broad variety of data migration, integration, and transformation tasks — and there are plenty of good reasons to use it. With Transact-SQL (T-SQL), Microsoft provides an equivalent technique for transformation tasks.

Discussions about whether to use SSIS solely for ETL development versus relying on T-SQL Stored Procedures and Functions often end in controversial debates — developers tend to stick to THEIR preferred choice. There is no general right or wrong. Depending on the requirements, the best solution is very often a combination of both SSIS and T-SQL. Other helpful techniques exist as well, for instance PowerShell scripts.

When comparing ETL technologies, benchmark tests are frequently cited. There may be areas where they matter for choosing the right technology, but in most cases they don’t — especially in times of essentially unlimited disk space, compute power, and throughput.

The Tool-Loyalty Trap

Tool choice often follows tool sympathy. Those who grew up with T-SQL see SSIS as an unnecessary graphical layer on top of what is really just SQL. Those who work with SSIS value the structuring effect of the graphical designer and find T-SQL an unwieldy mass of text. A design aspect reinforces this sympathy line: graphical ETL tools like SSIS deliberately target users with less SQL experience — the designer lowers the barrier to entry and makes ETL pipelines accessible to BI practitioners without a deep T-SQL background. All of these arguments are understandable — but they don’t hold up as architecture arguments, because they attach to the tool rather than to the requirements.

The question is not which tool is better in general, but which architectural property matters in the concrete case: readability of a diff, permission context of a job step, connector breadth for file integration, version-control friendliness of an artifact. Anyone arguing at this level arrives at reproducible decisions — anyone arguing tool loyalty arrives at many small ones.

What T-SQL Does Well

T-SQL wins wherever readability, versionability, and set-based performance matter:

  • Set-based operations — JOINs, aggregate window functions, recursive CTEs — are more compact and faster in T-SQL than in SSIS pipelines with data-flow buffers.
  • Versionability — a Stored Procedure is plain-text SQL and produces a line-readable diff. A .dtsx package creates a GUID-reordering earthquake in XML on minimal changes — see Source Code Management.
  • Readability in code review — pull-request diffs on plain SQL are accessible to a reviewer in 30 seconds; the graphical data-flow designer isn’t. See Readability/Maintainability for the developed argument on a hierarchy-ranking example.
  • Mathematical and analytical transformations — window functions, GROUPING SETS, MERGE — are first-class in T-SQL. In SSIS you’d chain script tasks or multiple data-flow components.

What SSIS Does Well

SSIS wins wherever the ETL job reaches beyond the database boundary:

  • File-system integration — FTP, CSV, Excel, XML, directory traversal, file-movement tasks. T-SQL can do this with BULK INSERT and OPENROWSET, but SSIS is far more pragmatic with its standard connectors.
  • Connector breadth — Oracle, DB2, ODBC, OLE-DB, SAP, Salesforce: SSIS ships the out-of-the-box drivers that a pure-T-SQL setup would have to configure manually as Linked Servers or External Tables.
  • Pipeline buffer strategy and parallelization — the data-flow task uses buffer pipelining: one row is read, transformed, and written while the next is already in the read buffer. For long transformation chains this can dominate memory footprint and throughput.
  • Job scheduling via SQL Server Agent + impersonation — Agent jobs can run SSIS packages under a proxy user with a dedicated credential. T-SQL script steps don’t have Run As, which is why for file-share access or cross-instance authentication the SSIS wrapper is often the only clean path — see Impersonation.
  • Operator tooling and logging infrastructure — the SSIS Catalog (SSISDB) brings structured execution logs, parameter overrides, and reporting without requiring custom logging tables.

When to Combine What

The pragmatic synthesis rarely arrives as pure T-SQL or pure SSIS — it arrives as a combined pattern:

  • SSIS as an orchestration wrapper: SSIS packages provide file integration, connector mapping, and Agent scheduling. The actual transformation substance lives in Stored Procedures that the data flow or an Execute SQL Task invokes. This keeps SQL diffs readable, the version-control argument intact, and the SSIS advantages (file connectors, Run As, Catalog logging) preserved. See Readability/Maintainability for three concrete solution variants on a single example.
  • Heuristic: If the step is CSV import + transformation + INSERT → SSIS wrapper with SP calls. If the step is pure SQL set operations → pure Stored Procedure, called directly by the Agent as a T-SQL script step (or inside the wrapper, if Run-As is required).
  • Anti-pattern: hiding complex SQL logic inside an OLE-DB-Source component of a data flow — the code isn’t versionable, isn’t reviewable, and is hard to test. That substance belongs in a Stored Procedure.

Three Decision Criteria in the Cluster

The three axes against which every concrete tool and extent-of-use choice should be measured are developed as their own cluster articles. Although the cluster argues with SSIS and T-SQL as the example stack, the three criteria apply across tools — anyone working with Talend Open Studio, Pentaho / Kettle, Informatica, or Qlik Data Integration faces the same questions: readability of a diff, versionability of an artifact, permission context of a job step. The artifact names change; the maintainability and security properties don’t.

  • Readability/Maintainability — How much SQL belongs in an SSIS package? Three solution approaches for the same ETL example, evaluated along five dimensions (development time, readability, maintainability, performance, functional scope).
  • Source Code Management — Why SP diffs are readable and .dtsx diffs aren’t. The maintainability decision beyond tool choice: which artifact format supports version control, and which doesn’t?
  • Impersonation — Proxy user, credential, the Run As property per step type. Mandatory reading once Agent jobs need to access file shares or cross-instance resources — and the reason why SSIS packages become indispensable as wrappers for otherwise pure-SP pipelines.

ETL 2026

The original question „SSIS and/or T-SQL?” was asked in 2018 within the on-prem Microsoft stack. Eight years later, the tool landscape has expanded — the question remains relevant, the answer has shifted.

Microsoft Stack Today

SSIS remains supported (SQL Server 2022 included), and the Azure-SSIS Integration Runtime lets you run existing packages on Azure Data Factory (ADF) — the lift-and-shift path is intact. For new ETL projects in the Microsoft cloud stack, however, SSIS is no longer the default; ADF itself (with Mapping and Wrangling Data Flows) or Synapse Pipelines in the Synapse Analytics context is. Since 2024, Microsoft has been consolidating the cloud ETL world under Microsoft Fabric Data Factory and now explicitly positions it as „the next generation of Azure Data Factory” — including a PowerShell migration tool for moving from ADF to Fabric. Synapse Pipelines is moving into Fabric going forward as well. Standalone ADF remains in place for existing pipelines and lift-and-shift scenarios (in particular the Azure-SSIS Integration Runtime, which has no direct counterpart in Fabric Data Factory) — new projects are increasingly set up in Fabric. The diff problem (JSON pipeline definitions instead of XML .dtsx) shifts location without being structurally solved.

Postgres- and SQL-Centric World

Outside the Microsoft stack, three tool classes have established themselves and re-cut SSIS functionality:

  • Postgres-native primitives (LATERAL, Foreign Data Wrappers, COPY) replace many connector and file-integration scenarios that previously required SSIS.
  • dbt is the standard for transform-layer versioning — everything as SQL-as-code, with Git diff readability, tests, and lineage. Exactly what .dtsx packages structurally cannot deliver.
  • Airflow, Prefect, or Dagster orchestrate the pipeline (what the SQL Server Agent used to do), typically container-based and with first-class code versioning.

Anyone running a Postgres, BigQuery, or Snowflake stack rarely needs SSIS — the ETL-2026 world picks from dbt + an orchestrator + native database constructs.

The Question Remains: What Maps Maintainably?

What the three cluster axes (readability, source code management, impersonation) capture isn’t specific to SSIS. It’s the general question of which artifact format supports version control, code review, and granular security contexts — and which doesn’t. SSIS sits on the difficult side (XML, GUID reordering, compact wrapper function for authentication). ADF and Synapse Pipelines fare better with their JSON definitions, but not in the dbt/SP league. Plain SQL — whether in a Stored Procedure or a dbt model — remains the format with the highest maintainability per engineering hour.

Take-Away

  • The question „SSIS or T-SQL?” is an architecture decision, not a tool-loyalty question.
  • T-SQL wins on readability, versionability, and set-based performance; SSIS wins on file integration, connector breadth, and job scheduling with impersonation.
  • The pragmatic synthesis: SSIS as an orchestration wrapper, SQL substance in Stored Procedures.
  • 2026: the question remains relevant, but the answer shifts toward the cloud (Microsoft Fabric Data Factory as the official ADF successor, Synapse Pipelines moving along) and versionable transform tools (dbt, Postgres-native primitives).

FAQ

Should I still start new ETL projects with SSIS in 2026?

In the classic on-prem SQL Server stack, yes, if file integration and Agent job scheduling are central — SSIS is still the pragmatic tool there. In a cloud or Postgres stack, probably not: Azure Data Factory / Microsoft Fabric Data Factory or dbt + Airflow cover the same tasks with better version-control and diff pragmatics.

What’s the pragmatic middle ground between pure T-SQL and pure SSIS?

SSIS as an orchestration wrapper that handles file integration, connector mapping, and Agent scheduling — the actual transformation substance lives in Stored Procedures that the SSIS package invokes. This keeps SQL diffs readable, version control intact, and SSIS advantages (Run As, SSISDB Catalog logging, connector library) preserved. See Readability/Maintainability for three concrete solution variants on a single example.

Where is the real value of SSIS over pure T-SQL?

Three areas: first, file-system integration and connector breadth (FTP, CSV, Excel, Oracle, SAP — all out of the box). Second, pipeline buffer strategy with overlapping read/transform/write that dominates throughput in long transformation chains. Third, Run-As requirements for Agent jobs that need access to file shares or cross-instance resources — T-SQL script steps don’t support Run As, SSIS packages do (see Impersonation).

Which modern tools replace SSIS in cloud or Postgres stacks?

In the Microsoft cloud stack: Azure Data Factory and Microsoft Fabric Data Factory (Fabric has been consolidating ADF and Synapse Pipelines since 2024). In the open stack: dbt for transform-layer versioning (SQL-as-code with Git diffs and tests), Airflow / Prefect / Dagster for orchestration, Postgres-native primitives (LATERAL, FDW, COPY) for many connector scenarios.

Is Microsoft Fabric Data Factory the direct SSIS successor?

Not directly. Fabric Data Factory is officially the successor of Azure Data Factory — Microsoft Learn frames it as „the next generation of Azure Data Factory”. SSIS packages continue to run via the Azure-SSIS Integration Runtime in ADF — Fabric Data Factory has no direct counterpart. Functionally, Fabric pipelines cover the typical SSIS tasks (file import, pipeline orchestration, connector mapping) with JSON-based pipeline definitions instead of XML .dtsx. Anyone moving away from SSIS has two paths: lift-and-shift into the Azure-SSIS IR (short term) or re-implement in Fabric Data Factory or ADF-native pipelines (long term).

Do these decision criteria also apply to other ETL tools like Talend, Pentaho, or Informatica?

Yes. The cluster argues with SSIS and T-SQL as the example stack, but the three axes — readability, source code management, impersonation — are generic ETL tool concerns. Talend Open Studio, Pentaho / Kettle, Informatica, and Qlik Data Integration differ in their concrete artifact formats (XML, JSON, proprietary containers) and in their security models (service accounts, Run-As equivalents, credential stores), but the structural maintainability questions are the same. Anyone coming from one of those stacks can apply the cluster’s reasoning one-to-one to their tool world — only the artifact names and API calls change. The synthesis „orchestration wrapper tool plus SQL substance in Stored Procedures” works analogously with Talend or Pentaho jobs as the wrapper.

The three cluster deep-dives, each with a preview:

  • Readability/Maintainability — How much SQL belongs in an SSIS package? Three solution approaches for a hierarchy-ranking example on AdventureWorksDW2017, evaluated along five dimensions.
  • Source Code Management — Why SP diffs are readable and .dtsx diffs aren’t. A maintainability decision beyond tool choice, with a Version-Control-2026 framing (Git, dbt, sqlmesh, Liquibase/Flyway).
  • Impersonation — When an Agent job needs to read a file share: proxy user + credential as the mandatory pattern. T-SQL script steps don’t have Run As — SSIS packages do.

Further ETL-context cross-links: