The Configuration Layer You're Missing

Shannon Lowder

Shannon Lowder

— 3 min read

Here's a problem I see on almost every data project: the SSIS package has the server name hardcoded. Or the credentials are baked into the connection manager. Or the file paths are literals inside the data flow. Everything works fine in development. Then it goes to production and someone has to manually edit connection strings in every package.

If you've shipped a package that required someone to open it in the BIDS designer and type in a new server name, you've felt this problem. It's not just annoying — it's a signal that your system doesn't have a proper configuration layer.

What a Configuration Layer Actually Is

A configuration layer is the part of your system that separates "what should this do" from "where should it do it." In software development, this is so fundamental that it barely gets discussed — of course you don't hardcode database connection strings into your application. You use a config file, environment variables, a secrets manager.

Data engineering inherited a different culture. ETL tools were built for people who were closer to the data than to software development, and the defaults often push you toward embedding configuration inside the artifact. SSIS's native .dtsConfig format was one answer — but it's file-based, scattered across the filesystem, and easy to lose track of.

There's a better approach: put your configuration in a database table.

The Configuration Table Pattern

CREATE TABLE etl.Environment (
    EnvName         NVARCHAR(50)    NOT NULL,
    ConfigKey       NVARCHAR(100)   NOT NULL,
    ConfigValue     NVARCHAR(500)   NOT NULL,
    Description     NVARCHAR(500)   NULL,
    UpdatedAt       DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedBy       NVARCHAR(100)   NOT NULL DEFAULT SUSER_SNAME(),
    CONSTRAINT pk_Environment PRIMARY KEY (EnvName, ConfigKey)
);

-- Populated once per environment
INSERT INTO etl.Environment (EnvName, ConfigKey, ConfigValue, Description)
VALUES
('DEV',  'SourceServer', 'DEV-SQL-01',       'Source system server name'),
('DEV',  'ArchivePath',  'D:ETLArchive',  'Root path for archived files'),
('PROD', 'SourceServer', 'PROD-SQL-CLUSTER', 'Source system server name'),
('PROD', 'ArchivePath',  'E:ETLArchive',  'Root path for archived files');

Your SSIS packages read from this table at runtime using a SQL Execute task at the top of the control flow. The environment name comes in as a package parameter — the one thing you do configure externally, because it's the single value that identifies which environment you're running in.

-- Package control flow: read config first, then use variables
SELECT ConfigValue
FROM   etl.Environment
WHERE  EnvName   = ?   -- package parameter: 'DEV' or 'PROD'
AND    ConfigKey = 'SourceServer';

Why a Database Table Beats a Config File

SSIS's native configuration files have two problems: they live on the filesystem, which makes them hard to audit and easy to lose; and they require file system access from the server running the package, which creates a deployment dependency you don't want.

A database table gives you three things config files can't:

Audit trail. The UpdatedAt and UpdatedBy columns mean you know when a configuration changed and who changed it. When something breaks after a deployment and the answer turns out to be "someone changed the archive path," you can find out immediately instead of spending half a day guessing.

Queryability. Need to know everything that points to the production server? One query. Try doing that with scattered .dtsConfig files across a dozen package directories.

Single source of truth. All packages read from the same table. Changing a value once changes it everywhere. No more "I updated the dev config but forgot the staging one."

What This Enables

Once you have a proper configuration layer, environment promotion gets simple: add a row for the new environment and you're done. New environments are additive — no package code changes required. Runtime auditing becomes possible because you can log which configuration values were active for each package run.

You've also separated a concern that was previously tangled up inside your packages. The packages know how to run. The configuration table knows where and with what. Those should always be separate things.

This is one of those patterns that feels like extra work the first time and pays dividends on every project after. If you've already implemented something like this and run into gotchas I haven't covered, I'd love to hear about it. As always, I'm here to help.

Read more