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.
The data warehouse automation solution trusted by data teams across industries
Does this sound familiar?
- The warehouse is moving to Fabric or Snowflake, and the plan for the packages is a VM in the cloud that runs them as before.
- Loading a non Microsoft source or target means a third party connector with its own license and its own upgrade cycle.
- Large loads run through the SSIS data flow buffer on one server, and the server is the limit.
- The ETL box needs a SQL Server license of its own, for a workload that is neither the source nor the warehouse.
SSIS was engineered for a world where the source and the warehouse were both SQL Server, often in the same data centre. It is very good at that. The friction starts when the warehouse moves to Fabric, Snowflake, Databricks or BigQuery, and the packages have to follow.
Why SSIS stops at the platform edge
- The package assumes SQL Server on both ends. Non Microsoft targets mean third party connectors, each with a license, a version and an upgrade cycle of its own.
- Transformation runs on the SSIS box. Data crosses the network to the SSIS server, flows through the data flow buffer there, and crosses the network again to the warehouse. The server’s memory and cores are the ceiling, and every load pays the two hops.
- The ETL server needs its own license. SSIS ships with SQL Server, so the box that only runs packages still carries a SQL Server license for a workload that is neither source nor warehouse.
- The cloud path is a lift. Azure-SSIS Integration Runtime runs the packages as they are, on Azure. The estate arrives in the cloud with everything it had.
None of this is an SSIS fault. It was built for one platform and it serves that platform well.
Where the loads belong
- In the target engine. Fabric, Snowflake, Databricks and BigQuery are set based engines. A generated delta load runs inside them, on their compute, with no ETL server in between.
- In a model that does not care about the platform. Declare the warehouse once; generate it for the platform you have now and for the one you move to.
- Without carrying the packages. A migration is the moment to stop maintaining hundreds of hand built loads, not to host them somewhere new.
What changes with Datavault Builder
Datavault Builder generates the warehouse natively for SQL Server, Azure SQL, Fabric, Snowflake, Databricks, BigQuery, Oracle and PostgreSQL from one model, with its own ingestion from on premises and cloud sources.
- The loads run in the warehouse. Set based delta SQL compiled for the target engine. No SSIS server, no data flow buffer, no extra license for an ETL box.
- Sources connect directly. Batch, delta and CDC from databases, files, REST APIs, NoSQL and Python sources, with streams such as Kafka as micro batches. No third party connector layer.
- The platform is a target. The same model generates for SQL Server today and Fabric or Snowflake tomorrow, each in its own SQL dialect and load pattern. The move is a regeneration, not a rewrite, and it works in the other direction too.
- The packages are retired, not hosted. Each source mapped in the model is a package that does not need a VM in the cloud.
- Nothing forces a big bang. Loads that stay between on premises systems can stay in SSIS until they are mapped.
What to decide
List the packages whose target is the warehouse that is moving. Those are the loads the lift would carry across unchanged, and the first ones to generate for the new platform instead.
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
-
List the packages by target
Which loads land in the warehouse that is moving. Those are the ones the lift would carry across unchanged.
-
Model them for the new platform
Business keys, relationships and attributes go into the Datavault Builder model. The loads are generated for Fabric, Snowflake, Databricks or BigQuery.
-
Keep SSIS for what stays on premises
Loads between on premises SQL Server systems can stay in SSIS until they are mapped too. Nothing forces a big bang.
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.
-
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.
-
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.
Questions and Answers
- It runs the packages unchanged on Azure, which is exactly the point: the package per table, the hand built data flows and the merge problem come along. It is a bridge, and Microsoft’s own direction is Fabric Data Factory, not SSIS in the cloud.
- Yes. Batch, delta and CDC from databases over JDBC, files, REST APIs, NoSQL and Python sources, with streams such as Kafka as micro batches, into a warehouse generated natively for the target platform.
- Then the generated warehouse runs on SQL Server or Azure SQL, and the modernization is of the ETL, not the platform. Moving to Fabric later is a target change.