Discover packages and their dependencies
An SSIS estate is more than its .dtsx files. A useful inventory covers:
- packages with their control flow and data flow tasks
- connection managers and the systems they point to
- project and package parameters, variables and environments
- stored procedures and SQL statements the packages execute
- file operations and the folders and shares they depend on
- parent and child package executions
- SQL Server Agent jobs and their schedules
- script tasks and components containing custom .NET code
If packages run from the SSIS catalog, its execution history adds what the files cannot: which packages actually run, how often, how long they take and how often they fail. That is valuable input for both scoping and orchestration design.
Classify before migrating
Once packages are decomposed, most fall into recognisable types, each with a different natural target:
| Package type | Likely Fabric target |
|---|---|
| Copy-oriented: move data from A to B with little logic | Pipeline copy activities or copy jobs |
| SQL-heavy: mainly executes SQL and procedures | Warehouse T-SQL, orchestrated by a pipeline |
| Complex data flow transformations | Spark notebooks or a redesigned transformation layer |
| Stored-procedure orchestrator: a package that calls procedures in sequence | Orchestration in a pipeline, with the SQL logic reviewed separately |
| File handling: pick up, archive, move | Pipeline activities on OneLake or connected storage |
| Script tasks with custom code | Rewrite; usually the most expensive part and worth identifying early |
Classification also reveals how much of the estate follows a handful of patterns, which is what makes a metadata-driven target framework worthwhile.
Avoid package-for-package recreation
The unit of migration is the business workload, not the .dtsx file. A single package can contain three unrelated loads that happen to share a schedule; five packages can together implement one load. Recreating each package as one Fabric pipeline or notebook preserves those accidents of history and makes the new platform harder to operate than necessary.
Running existing packages unchanged in Azure, for example on an Azure-SSIS integration runtime, can be a legitimate bridge when time is short. It moves the estate rather than modernising it, and it should be treated as a stage with an end date.
Stored procedures need review, not just translation
In many SSIS estates, the real business logic lives in stored procedures that the packages call. Fabric Warehouse supports a large part of T-SQL, but not everything SQL Server does, and procedures built around cursors, temporary tables, dynamic SQL or row-by-row processing may need to be redesigned as set-based logic rather than translated.
This is where AI-assisted analysis earns its place: reading a large body of procedures, documenting what each one does, flagging constructs that need attention and proposing target code, with an engineer reviewing every result. Our Agentic Reverse Engineering accelerator is built for that.
Configuration and secrets
SSIS configurations, environments and connection strings should not be copied literally. Environment-specific values belong in parameterised connections and deployment-stage configuration. Credentials belong in a proper secret store or managed identities, not in package parameters or configuration tables. Migrating is a good moment to remove passwords from places they should never have been.
Scheduling, restartability and concurrency
SQL Agent schedules, package precedence constraints, checkpoints and event handlers together define how the estate behaves when something fails. The new orchestration needs to preserve those semantics deliberately: dependencies between loads, retries, idempotent steps that can be rerun without duplicating data, and the ability to restart from the failed stage rather than replaying everything.
Parallelism deserves testing rather than assumptions. Running more loads concurrently shortens the batch window until contention appears, in Spark sessions, metadata operations, Delta commits, logging tables or downstream services. A framework that works for one pipeline can behave very differently when dozens start together.
How execution units are isolated is also a design decision. Separate sessions protect loads from each other’s state, temporary views and configuration changes, at the cost of start-up time and resources. Shared sessions and thread-based parallelism are faster to start but share more state. The right answer depends on the workload, and it should be chosen deliberately rather than inherited from the orchestration tool.
Validation
Every migrated load is compared with its SSIS counterpart on keys, values and aggregates, and for slowly changing dimensions on history. SSIS lookups with full cache and “ignore failure” settings, and the built-in Slowly Changing Dimension component, are common sources of behaviour that differs subtly after migration. See how to prove a migration is correct.
Questions we are often asked
Should each SSIS package become one Fabric pipeline?
Usually not. Packages often mix unrelated loads or split one load across several files. Migrating by business workload, and implementing repeated patterns once, gives a simpler and more maintainable result.
Can SSIS packages run in Azure without rewriting them?
Yes, for example on an Azure-SSIS integration runtime. That can be a useful bridge, but it moves the estate rather than modernising it, and it is best treated as a temporary stage.
What happens to the stored procedures SSIS calls?
They are analysed separately. Many can move to Fabric Warehouse with limited changes; those relying on unsupported features or row-by-row processing are redesigned, and all are validated on their outputs.