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

Read a CSV file, transform the data, load the result into SQL Server: for the file part, SSIS is the obvious choice, for the transformation pure T-SQL. The best solution is often a combination of both. There is no single right way, but there are three decision criteria against which every concrete choice should be measured: readability, source code management, impersonation. This article series takes on these three axes and delivers one concrete argument per axis.

At a glance:

• SSIS vs. SQL is an architecture question, not tool loyalty.
• Three deep-dive axes with their own articles: Readability/Maintainability, Source Code Management, Impersonation.
• Pragmatic synthesis for SQL-centric pipelines: SSIS as the orchestration wrapper, the transformation logic in stored procedures.
• The 2026 picture: Microsoft Fabric Data Factory, dbt, and Postgres built-ins shift the answer — the question remains.

Prerequisite: Working knowledge of SQL Server 2017+ and SSIS 2017+ (Visual Studio with SSDT).

Contents

• Overview
• The Tool-Loyalty Trap
• What T-SQL Does Well
• What SSIS Does Well
• When to Combine What
• Three Decision Criteria in the Cluster
• ETL 2026
• Take-Away
• FAQ
• Related Articles

Overview

SQL Server Integration Services (SSIS) is a powerful tool for building ETL pipelines. There are many good reasons to use it, and just as many for using it in moderation. If you limit yourself to the Microsoft product stack and set the Azure world aside for a moment, the main alternative for complex ETL pipelines is Transact-SQL (T-SQL).

Discussions about the right technology, SSIS and/or T-SQL, and about the extent of its use often end in philosophical debates among developers. There is no single right answer. Which mix is optimal depends on the requirements, and sometimes additional technologies join in. This article series looks at the decision criteria that matter.

Performance and benchmark tests are frequently cited in this choice. Runtime and throughput, however, play only a minor role here, because at most real-world interface volumes they are not the bottleneck. More on this in the FAQ.

The Tool-Loyalty Trap

The choice is often driven by tool sympathy. Those who grew up with T-SQL see SSIS as a superfluous graphical layer around what is really SQL. Those who work with SSIS value the structuring effect of the graphical designer and perceive T-SQL as an unwieldy wall of text. Add to that a design aspect that reinforces the sympathy divide: graphical ETL tools like SSIS deliberately also target users with less SQL experience. The designer lowers the entry barrier and makes ETL pipelines accessible to BI practitioners who don’t bring a deep T-SQL background. All of these arguments are understandable. As architecture arguments, they still don’t hold, because they are attached to the tool and not to the requirements.

The question is not which tool is fundamentally better, but which architectural property matters in the concrete case: readability of a diff, security context of a job step, connector variety for file integration, versionability of an artifact. Argue on this level and you get reproducible decisions. Argue with tool loyalty and you have the same debate all over again on the next project.

What T-SQL Does Well

T-SQL wins where readability, versionability, and set-based expressiveness matter:

• Set-based operations: JOINs, aggregate and window functions, and recursive CTEs can be expressed more compactly and closer to the data model in T-SQL than in a chain of SSIS data-flow components. Whether the T-SQL variant also runs faster depends on the scenario (see FAQ).
• Versionability: A stored procedure is plain-text SQL and produces a line-readable diff. A .dtsx package, by contrast, can trigger a GUID-reordering earthquake in the XML from even minimal changes. Why that happens is covered in 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 is not. The article Readability/Maintainability builds out this argument on a hierarchy-ranking example.
• Mathematical and analytical transformations: Window functions and GROUPING SETS are native T-SQL language features. MERGE is available as well, but its documented limitations call for a careful look. In SSIS, you would have to chain script tasks or multiple data-flow components for such logic.

What SSIS Does Well

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

• File-system integration: FTP, CSV, Excel, XML files, directory traversal, and file-movement tasks are covered by native SSIS tasks and components. T-SQL can read files with BULK INSERT and OPENROWSET and process them inside the engine afterwards. For the file orchestration around it, such as file discovery, moving, or FTP, SSIS offers far more ready-made building blocks.
• Connector variety: SSIS ships with a broad selection of connectors for relational databases, files, and enterprise systems. For systems like Oracle, DB2, or SAP, additional providers, drivers, or licensed components may be required depending on the scenario. In a pure T-SQL world, the same access would have to be set up manually as linked servers or external tables.
• Buffering and pipeline parallelism: The data-flow task processes data in buffers that each hold many rows. While one component transforms a buffer, other components can work on further buffers in parallel. Buffer size, transformation types, and the properties of source and target determine memory footprint and throughput.
• Job scheduling via SQL Server Agent with impersonation: An SSIS job step can run under an Agent proxy that is based on a credential, is granted to the SSIS subsystem, and provides a dedicated Windows security context. T-SQL job steps, by contrast, use no Agent proxies. Their database context derives from the job owner via EXECUTE AS, and they get no alternative Windows context. As soon as a job needs to access file shares or other instances, a proxy-capable subsystem is required — besides SSIS, that can also be a CmdExec or PowerShell step. SSIS is the natural choice when the job runs a package anyway, see Impersonation.
• Operator tooling and logging infrastructure: The SSIS catalog (SSISDB) provides technical execution logs, parameter overrides, and reporting without maintaining your own logging tables. Business-level ETL logs, such as the number of processed or rejected records, still need a concept of their own — an SQL solution for that is shown in Design Pattern // Logging an ETL Process with T-SQL.

When to Combine What

The two strength profiles in direct comparison:

Criterion	T-SQL	SSIS
Set-based transformation inside the engine	very strong	usually an unnecessary extra layer
File import	BULK INSERT, OPENROWSET	native tasks and components
File orchestration (FTP, moving, loops)	few built-ins	strong
Connectors to external systems	linked servers, external tables (manual)	broad connector selection
Git diff and code review	line-readable	XML diff hard to read
Agent proxy (dedicated Windows context)	no	yes

The pragmatic synthesis rarely comes as a pure T-SQL or pure SSIS solution, but as a combined pattern:

• SSIS as the orchestration wrapper: SSIS packages provide file integration, connector mapping, and Agent scheduling. The actual transformation logic, however, lives in stored procedures called by the data flow or an Execute SQL task. That keeps the SQL diffs readable, the version-control argument holds, and the SSIS benefits such as file connectors, proxy execution, and catalog logging remain. This pattern is best suited to SQL-centric pipelines whose data flows into the engine anyway. Where data is deliberately transformed in the data flow, such as when streaming between external systems, that remains a legitimate design decision. Three concrete solution variants on one example are shown in Readability/Maintainability.
• Heuristic: If a step consists of CSV import, data transformation, and INSERT, the SSIS wrapper with SP calls fits. If it consists purely of set-based SQL operations, a stored procedure called directly by SQL Server Agent as a T-SQL script step is enough. If the job needs a proxy security context, it runs in the SSIS wrapper as well.
• Anti-pattern: Hiding complex SQL logic in an OLE DB source component of a data flow. The SQL code then lives as a package property inside the .dtsx and is thus technically versioned, but barely diffable and reviewable, and hard to test in isolation. This logic belongs in a stored procedure.

Three Decision Criteria in the Cluster

The three axes against which every concrete choice of tool and approach should be measured are covered in depth in their own articles. Even though this series argues with SSIS and T-SQL as its example stack, the three criteria apply across tools. If you work with Talend Open Studio, Pentaho / Kettle, Informatica, or Qlik Data Integration, you face the same questions: readability of a diff, versionability of an artifact, security context of a job step. The artifact names change, the maintainability and security properties do not.

• 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, feature 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, which doesn’t?
• Impersonation — proxy user, credential, and the Run as setting of each step type. Required reading as soon as Agent jobs need to access file shares or cross-instance resources, and the reason why SSIS packages are often the fitting wrapper 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 broadened. The question remains relevant, the answer is shifting.

Microsoft Stack Today

SSIS remains supported, SQL Server 2022 included. Via the Azure-SSIS Integration Runtime, existing packages can run in Azure Data Factory (ADF), so the lift-and-shift path is intact. For new ETL projects in the Microsoft cloud stack, however, the default is no longer SSIS but ADF itself with its mapping and wrangling data flows, and in the Synapse Analytics environment also Synapse Pipelines. Since 2024, Microsoft has been bringing the cloud ETL world together under Microsoft Fabric Data Factory and explicitly positions it as the “next generation of Azure Data Factory”. For the move from ADF to Fabric, Microsoft provides a PowerShell migration tool. For Synapse Pipelines, Microsoft provides migration paths to Fabric as well. ADF stays relevant alongside, in particular for existing pipelines and for the Azure-SSIS Integration Runtime, which so far has no direct counterpart in Fabric Data Factory (as of September 2026). The diff problem thereby shifts from XML .dtsx to JSON pipeline definitions without being fundamentally solved.

Postgres- and SQL-Centric World

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

• Postgres built-ins: COPY covers efficient file imports and exports, foreign data wrappers plug external data sources directly in. Together they cover many import and connectivity scenarios that a SQL Server stack would often solve with SSIS. Overarching orchestration remains the job of dedicated tools. SQL constructs like LATERAL, on the other hand, extend the expressive options within the transformation.
• dbt has become widely established for versioning the transform layer: everything is SQL-as-code, with Git-diff readability, tests, and lineage. Exactly what .dtsx packages structurally cannot deliver.
• Airflow, Prefect, or Dagster orchestrate the pipeline and take over the role the SQL Server Agent had in the classic stack. Their pipeline definitions are code and thus cleanly versionable. Deployments range from containers via Kubernetes to classic worker environments.

If you run a Postgres, BigQuery, or Snowflake stack, you rarely come into contact with SSIS anymore. The ETL world of 2026 mostly picks from dbt, an orchestrator, and native database constructs.

The Question Remains: What Maps Maintainably?

What makes up the three axes of readability, source code management, and impersonation is not SSIS-specific. It is the general question of which artifact format supports version control, code review, and granular security contexts, and which does not. SSIS sits on the difficult side: XML artifacts, GUID reordering in the diff, and for the security context it scores mainly as a compact wrapper. ADF and Synapse pipelines are text-versionable as JSON artifacts. For SQL-centric transformations, plain SQL still remains the artifact that is easiest to diff, review, and test — whether it lives in a stored procedure or a dbt model. The more durable question is therefore not “SSIS or T-SQL?” but: which parts of the pipeline need an integration platform, and which belong in the database as SQL logic?

Take-Away

• The question “SSIS or T-SQL?” is an architecture decision, not a matter of tool loyalty.
• T-SQL wins on readability, versionability, and set-based expressiveness. SSIS wins on file integration, connector variety, and job scheduling with proxy impersonation.
• The pragmatic synthesis for SQL-centric pipelines: SSIS as the orchestration wrapper, the transformation logic in stored procedures.
• In 2026 the question remains relevant, but the answer shifts: in the Microsoft cloud towards Fabric Data Factory as the next Data Factory generation, in the open stack towards versionable transform tools like dbt and towards Postgres built-ins.

As a practical decision aid:

Starting position	Recommended path
SQL-to-SQL transformation within one instance	Stored procedure, called directly by the Agent as a T-SQL step
File import plus SQL transformation	SSIS wrapper outside, stored procedures inside
Many heterogeneous source systems	SSIS (on-prem) or ADF/Fabric (cloud) as the connector layer
Agent job needs file-share or cross-instance access	Step type with Agent proxy, typically SSIS
Cloud-native new project in the Microsoft stack	Fabric Data Factory, transformation in SQL
Postgres, BigQuery, or Snowflake stack	dbt plus orchestrator, native database constructs

FAQ

Related Articles

Cluster deep-dives:

• 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 job steps support no Agent proxies, SSIS steps do.

ETL context:

• Design Pattern // ETL Process Architecture (German only) — the broader architectural frame for SSIS/T-SQL pipelines. German-only article, no English counterpart yet.
• Data Quality in an ETL Process — the quality axis that’s often forgotten in the tool discussion.
• Design Pattern // Logging an ETL Process with T-SQL — a concrete logging solution in T-SQL, in case SSISDB catalog logging doesn’t fit or isn’t available.
• ETL vs. ELT — How to Tell Which Pattern You Actually Built — the macro level of the same question: ETL vs. ELT as architecture, not tool choice.