Do Your SSIS Packages Outnumber the People Who Understand Them?
Hundreds of .dtsx packages, one per table, each built in Visual Studio by whoever had the ticket, with control flows and data flows that only open one at a time. Adding a column means opening the packages one by one. The fix is not a package template. It is a model that generates the loads, natively on SQL Server, Azure SQL or Fabric.
The data warehouse automation solution trusted by data teams across industries
Does this sound familiar?
- A new source column means opening every package that touches the table, in Visual Studio, one at a time.
- There is a package per table, and the ones built before project parameters existed still carry their own connection strings.
- Nobody is sure which packages are still scheduled, and nobody is deleting any to find out.
- Adding audit columns to the warehouse was estimated in weeks, because it is the same edit in hundreds of data flows.
SSIS was the right answer for two decades of SQL Server warehouses, and a mature estate shows it: hundreds of packages, built by many hands across many years, each opening one at a time in Visual Studio. The packages still run. Changing them is the problem.
Why the estate is hard to change
- One package per table, each built by hand. The same control flow and data flow, assembled again for every source, in the style of whoever had the ticket that week.
- Older packages carry their own configuration. Project parameters and shared connection managers exist since 2012; inherited packages built before that, or without them, still hold connection strings and paths inside.
- A schema change is a tour of packages. Adding a column to the warehouse means opening every package that touches it. The estimate is in weeks because the edit is in hundreds of data flows.
- A wider column is a broken data flow. Data flow metadata is typed and fixed at design time, so a source column that grows or changes type fails the component until somebody remaps it in Visual Studio.
- Nobody knows what is live. Packages are scheduled from SQL Agent, from SSISDB, from other packages, and nobody wants to be the one who deleted the wrong one.
None of this is an SSIS fault. A package is a good way to build one load and a poor way to hold a warehouse.
Where the structure belongs
- In a model, not in packages. Business keys, relationships and attributes declared once, with the loads derived from them.
- Generated, so every load has one shape. No per developer style, no package that only its author can read.
- On the platform you already run. SQL Server and Azure SQL are first class targets, and Fabric is there when the move comes.
What changes with Datavault Builder
Datavault Builder holds the warehouse in a visual model and generates the staging, historization and delivery loads from it, natively for SQL Server, Azure SQL and Fabric.
- The package per table goes away. Hubs, links, satellites and their loads are generated from the model. There is nothing to open in Visual Studio.
- A column is a mapping change. Map it, regenerate, and every dependent structure is updated consistently. Missing columns load as null with a warning; growing columns are widened.
- Every load has the same shape. The generator writes them, so there is no style drift and no package only its author understands.
- The model is the inventory. What is loaded, from where, into what, with lineage, is visible in one place. No hunting through SQL Agent jobs.
- Nothing has to leave SQL Server. The generated warehouse runs where the packages ran, and moves to Fabric as a target change when you decide to.
What to decide
Audit the estate and count the packages that exist only to load one table from one source into staging or the warehouse. That count is the size of the layer a model should generate. Sorted by source, the same list gives you the order in which to retire the packages.
See It Running on One of Your Sources
Book a free demo and bring the connector that costs you the most, in money or in time.
Three Steps to a Pipeline You Control
-
Count the packages that only load a table
Most of a mature SSIS estate is one package per source table doing the same thing. That is structure, not logic.
-
Model the sources instead
Business keys, relationships and attributes go into the Datavault Builder model. The loads are generated, natively on SQL Server, Azure SQL or Fabric.
-
Retire packages by mapping
Each source mapped in the model is a package that no longer needs to be opened. The estate shrinks as the model grows.
How Datavault Builder Takes the Friction Out of Ingestion
-
Ingestion is built in
Batch, delta and CDC loads from databases, files, REST APIs, NoSQL and Python sources, with streams such as Kafka arriving as micro-batches. Same platform that generates the warehouse, no second invoice.
-
Your schema, not the vendor's
Source tables are mapped to a Data Vault 2.0 model you designed. A new column or a renamed table changes a mapping, not a chain of post-load scripts.
-
Only deltas move
Hubs, links and satellites load what changed. Full reloads stay in staging instead of being reprocessed downstream every night.
-
History is kept by design
Every change is retained as it arrives, so as-was reporting works even where the source overwrites its own rows.
-
Code you never hand-write
Loading, historization and lineage are generated from the model in real time and run natively on Snowflake, Databricks, BigQuery, SQL Server, Fabric, Oracle or PostgreSQL.
-
One platform, up to nine tools fewer
Modeling, ETL, CI/CD, documentation and lineage in one place. That is what makes 14.7 minutes from requirement to production possible.
Recognized by BARC in The Data Fabric Survey 26
Meet Our Expert
Twenty minutes with our Sales Director, and an honest answer on whether this fits your stack.
Matt Collett
Sales Director
Great, pick a time that works for you:
Other Problems This Series Covers
-
SSIS Pain Points: Why Lifting Packages to the Cloud Moves the Debt, Not the Problem
SSIS estates have three problems that a cloud VM does not fix: a package per table nobody wants to open, releases that DevOps cannot reach, and a design that stops at SQL Server. Azure-SSIS Integration Runtime carries all three to Azure intact. A model that generates the warehouse retires them instead.
-
Does SSIS Stop Where Your Cloud Warehouse Starts?
SSIS was built to move data between on premises SQL Server instances. When the warehouse moves to Fabric, Snowflake, Databricks or BigQuery, the choices are lifting the packages onto an Azure-SSIS Integration Runtime, buying third party connectors, or rewriting. A model that generates natively for the new platform is the fourth choice, and the only one that does not carry the packages along.
-
Is SSIS the One Part of Your Stack That Still Cannot Do CI/CD?
A .dtsx file is XML that diffs badly and merges worse, so two engineers on one package end in a rebuild. Environments live in SSISDB variable mappings maintained by hand. Modern DevOps stops at the SSIS project. Releases should be generated from a model, per environment, with the rollback included.
Questions and Answers
- No. Datavault Builder generates the warehouse natively for SQL Server and Azure SQL, and for Fabric when you move. The ETL modernizes without the platform having to.
- It removes the drawing, and that is real progress. What it leaves is the estate: BIML produces .dtsx packages, so there are still hundreds of them to deploy, schedule, merge and run on an SSIS server. The difference is generating set based loads that run inside the database from a model, so there are no packages at all.
- They keep running until their source is mapped in the model, then they are retired. There is no big bang; the estate shrinks one mapping at a time.
- Business rules move into the business vault, where they are versioned and visible in the model. The loading logic, which is most of a typical package, is generated and never written again.