The Standard SSIS Package: Building a Template You Never Have to Rebuild

Shannon Lowder

Shannon Lowder

— 3 min read

The first SSIS package I ever built from scratch took most of a day. The second one, starting from a blank canvas, took almost as long. Not because the second job was complicated — it was a straightforward load from a flat file into a staging table — but because I spent the first hour reconstructing the same structural decisions I had made the day before. Where does the logging go? Which variables are standard? How does the error handler wire up? What's the naming convention for connection managers?

By the fifth package I had learned the wrong lesson: I had gotten faster at rebuilding the same thing from scratch. The right lesson was that I should not be rebuilding it at all.

What a Template Package Actually Contains

A template SSIS package is not a partially-filled package. It is a complete, runnable skeleton that handles everything structural so you can focus exclusively on the business logic. Here is what goes into mine:

Standard package-level variables:

User::PackageName     String   (default: package name, populated at runtime)
User::RunDate         DateTime (default: today)
User::RowsInserted    Int32    (default: 0)
User::RowsUpdated     Int32    (default: 0)
User::RowsErrored     Int32    (default: 0)
User::ErrorMessage    String   (populated by error handler)
User::Debug           Boolean  (default: false)

Standard control flow:

  1. Execute SQL Task — "Log Package Start" — inserts a row into the run log with status "Running"
  2. Sequence Container — "Main Work" — this is where your actual data flow goes
  3. Execute SQL Task — "Log Package Success" — updates the log row with status "Success" and row counts

The error handler on the package (in the Event Handlers tab) has its own Execute SQL Task that updates the log row with status "Failed" and writes @[System::ErrorDescription] into the error message column. This fires whether the failure happens in the data flow, a connection manager, or anywhere else the package controls.

Standard connection managers:

  • CM_OperationsDB — points to the operations database that holds the run log
  • CM_TargetDB — parameterized via package configuration or environment variable, pointing to the current environment's target

Everything else is business-specific and gets added per-package.

The Run Log Table

The template is useless without somewhere to write. The operations database gets this table on day one:

CREATE TABLE dbo.PackageLog (
    LogID          INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PackageName    NVARCHAR(255)  NOT NULL,
    RunDate        DATE           NOT NULL,
    StartTime      DATETIME2      NOT NULL,
    EndTime        DATETIME2      NULL,
    RowsInserted   INT            NULL,
    RowsUpdated    INT            NULL,
    RowsErrored    INT            NULL,
    Status         NVARCHAR(20)   NOT NULL DEFAULT 'Running',
    ErrorMessage   NVARCHAR(MAX)  NULL,
    ServerName     NVARCHAR(128)  NOT NULL DEFAULT HOST_NAME()
);

The "Log Package Start" task inserts with status "Running" and captures @@IDENTITY into a variable so the success/failure updates target the right row even if two instances of the package run concurrently.

What This Buys You

When someone asks whether a package ran last night, the answer is a two-second query. When a package fails, the error message from SSIS is in the database — you do not need to track down an email notification or parse a job history log. When you need to compare this month's row counts to last month's to verify a backfill completed correctly, that data is already there.

More importantly: every package built from this template looks structurally identical. When a junior developer or a client's internal team takes over maintenance, they already know where the logging is, what the variables mean, and where to drop the business-specific logic. The template encodes the decisions once so they do not have to be re-derived for every package.

Keeping the Template From Drifting

The template lives in source control. When the logging table schema changes — and it will, eventually — you update the template and note the version. Old packages keep running. New packages get the updated logging. You never find yourself in the situation where half your packages log ServerName and half do not, because someone built from the template before the column was added and never noticed.

This sounds obvious. It is not standard practice. Most SSIS shops I have walked into have packages that are structurally inconsistent because nobody designated a canonical template and enforced it. The result is that every package requires individual archaeology before you can trust it enough to modify it safely.

A shared template is a shared standard. It is cheaper to define it once than to negotiate it on every package review. If you have a different approach to standardizing the package skeleton — especially if you have solved the variable naming problem differently — I'd like to hear it. As always, I'm here to help.

Read more