SSIS or T-SQL for a Simple Load

A nightly copy between two SQL Server tables does not always need a visual pipeline. SSIS or T-SQL is a choice about the work and the team that will run it. Start with the simplest load that is clear, restartable, and observable.

Start the SSIS or T-SQL Choice With the Data Path

List source, destination, volume, schedule, transformations, and error handling. If both tables are in SQL Server and the load is a set-based insert or update, a stored procedure can be enough. If sources include files, APIs, or different providers, SSIS can make extraction and orchestration clearer.

I ask what happens on the second run after a partial failure. A one-page diagram that cannot answer that question is not a load design. The tool choice comes after the restart rule.

When T-SQL Is Enough

A simple database-to-database load can stage rows, validate keys, and apply them in a transaction. SQL Server Agent can schedule the procedure on a self-managed instance. Keep parameters for source range and run ID. Log start, end, counts, and errors. Use set-based operations instead of a cursor that moves one row at a time.

I favor T-SQL when the team already owns the database and the whole workflow is visible in a few statements. A package would add deployment and runtime surfaces without improving the data path.

Build a Bounded Insert

The example inserts stage rows absent from the target, using a unique business key. The target should enforce that key with a unique constraint. Run it in a test environment with a controlled batch. Real loads also need duplicate-source validation and a watermark or batch ID.

I count candidates before insertion and capture the affected row count after commit. Rerunning the same batch should not add duplicates.

INSERT INTO dbo.SalesTarget
    (SourceSaleID, SaleDate, Amount)
SELECT s.SourceSaleID, s.SaleDate, s.Amount
FROM dbo.SalesStage AS s
WHERE s.BatchID = 42
  AND NOT EXISTS
      (SELECT 1
       FROM dbo.SalesTarget AS t
       WHERE t.SourceSaleID = s.SourceSaleID);

When SSIS Earns Its Place

SSIS helps when the flow has multiple sources, file parsing, data-flow transformations, error outputs, and a deployment model the team can operate. It can connect components visually and record execution details in SSISDB. It still needs source queries, destination indexes, and restart logic designed well.

I avoid choosing SSIS solely because an old course used it for every import. A simple load can become harder to troubleshoot when its logic is split among package properties, expressions, and scripts. Use the tool for complexity it actually handles.

Handle Bad Rows Explicitly

A SQL procedure can reject invalid dates or duplicate keys into a review table. SSIS can redirect error rows from a data flow. With SSIS or T-SQL, either route needs a clear rule for whether the batch stops or continues. Never let a successful job status mean some rows were silently discarded.

This query finds duplicate source keys before a load. It runs equally well as a T-SQL preflight or an Execute SQL Task. I save the count and sample keys with the run log.

SELECT SourceSaleID,
       COUNT_BIG(*) AS source_rows
FROM dbo.SalesStage
WHERE BatchID = 42
GROUP BY SourceSaleID
HAVING COUNT_BIG(*) > 1;

Compare SSIS and T-SQL Deployment Work

A stored procedure deploys with database schema and permissions. An SSIS project needs package deployment, SSISDB configuration, environment references, and an execution schedule. Both need version control in the team’s existing delivery process, rollback, and a test. Choose the operational model the team can support after the author moves on.

I include connection secrets and service accounts in the comparison. A package that uses a laptop path is not production-ready. A procedure that assumes a linked server will always be online has its own dependency.

Measure the Whole Run in SSIS or T-SQL

Time extraction, transformation, target write, validation, and commit. Count source, inserted, updated, rejected, and unchanged rows. Test with representative volume and concurrent readers. A fast data-flow component does not make the job fast if a target index rebuild takes longer afterward.

I compare total elapsed time and resource use rather than counting boxes in the package. The simplest tool can lose if it forces unnecessary data movement. The more sophisticated tool can lose if it hides a row-by-row command.

Design the Failure Path

Stop after a failed validation, keep the source batch identifiable, and make reruns idempotent. If the job writes several targets, decide whether one transaction is practical or whether each step has a completion marker. Log the last successful step and the reason for failure. An operator should not need to read the full package to restart it.

I run one deliberate failure in a test environment. A load that resumes cleanly after an interrupted destination write is ready for more trust than one that only passed a clean run.

Choose by Maintainability

Pick T-SQL for a compact set-based path that the database team can test and operate. Pick SSIS when external sources, transformations, and package operations justify its runtime. Hybrid designs are normal: SSIS can move files into staging, while T-SQL performs set-based validation and merge.

SSIS or T-SQL for a simple load has no universal winner. The best choice makes the data movement obvious, complete, and recoverable. A diagram with fewer boxes is pleasant, but an operator sleeping through the night is better.

The comparison should include the whole operating path. A stored procedure can be the shortest solution for a source and target inside one SQL Server. It still needs a schedule, a run log, retry rules, and an owner. SSIS brings connectors and visual data flows, but it also brings project deployment and environment settings.

I prototype the simplest representative load in both forms only when the choice is unclear. Measure source read, transformation, target write, and restart effort separately. A faster first run is not enough if a failed batch is hard to replay. If the source is a file with complex format changes, SSIS can earn its place. If the work is a bounded set-based transformation, T-SQL can remain wonderfully plain. Choose the path the team can support after the original author has moved on.

Which path can your team restart safely after a partial failure, and which one can they explain without the original author present?

Related reading on this blog: Incremental Loads: Moving Only What Changed and What Is ETL? Extract, Transform and Load Explained.

A load tool is not the design, it is the place where a clear data contract runs.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.