Modernizing SSIS Workloads
Why this topic matters
Section titled “Why this topic matters”“Should we still be using SSIS?” is the wrong question, because it has no single answer for an estate of two hundred packages. The useful question is per-workload: for this set of packages, is the cheapest correct move to leave it alone, host it in the cloud unchanged, or rebuild it in something else?
All three answers are legitimate, and a large estate will contain all three. The expensive mistake is applying one answer uniformly — usually “migrate everything”, because a migration project exists.
Fundamentals: the four options
Section titled “Fundamentals: the four options”| Option | What changes | What does not | Typical driver |
|---|---|---|---|
| Keep | Nothing | Everything | It works; sources are staying put |
| Lift and shift | Where packages run | The packages themselves | Data-centre exit, infrastructure cost |
| Re-orchestrate | What triggers packages | The packages themselves | Orchestration pain, cross-system dependencies |
| Redesign | Everything | The business logic (ideally) | Sources moved; the package is a thin wrapper |
A fifth option is usually the cheapest and is routinely forgotten: retire. Some packages feed reports nobody reads.
The mental model: triage before tooling
Section titled “The mental model: triage before tooling” ┌────────────────────────┐ │ Is it still used? │ └───────┬────────┬───────┘ no │ │ yes ▼ ▼ ┌────────┐ ┌──────────────────────────┐ │ RETIRE │ │ Are its sources/targets │ └────────┘ │ staying on-premises? │ └────┬─────────────┬───────┘ yes │ │ no ▼ ▼ ┌────────────────┐ ┌──────────────────────┐ │ Is operations │ │ Is it simple AND │ │ adequate? │ │ actively developed? │ └──┬──────────┬──┘ └────┬────────────┬────┘ yes │ │ no yes │ │ no ▼ ▼ ▼ ▼ KEEP RE-ORCHESTRATE REDESIGN LIFT & SHIFT (invest in CI/CD, (then reassess, logging, source or never) control)Every branch of that tree is a defensible engineering answer. The tree exists so that the answer comes from the workload’s properties, not from the project’s name.
Assessment: what actually decides difficulty
Section titled “Assessment: what actually decides difficulty”Before choosing a destination, inventory what you have. The things that decide difficulty are rarely the transformations.
| Question | Why it decides the answer |
|---|---|
| Where do sources and targets live? | Packages reading on-premises file shares and databases carry that constraint wherever they run |
| Does the package touch the local file system? | Absolute paths and UNC shares are the most common migration blocker |
| Which components are used? | Third-party, custom, and deprecated components each need a plan |
| How much Script Task/Component code is there? | Code moves, but it is also where undocumented behaviour hides |
| What authentication does it use? | Windows authentication to on-premises resources needs explicit configuration in the cloud |
| How is it scheduled, and what depends on it? | Coupling is usually wider than the team remembers |
| How often does it change? | A package untouched in four years is a poor investment target |
| Does it still deliver value? | Retire beats migrate |
| What is its protection level? | Password-based protection complicates every automated path |
Components needing a specific plan
Section titled “Components needing a specific plan”Two deprecations matter for any assessment done now:
- The SSIS CDC Flow Components — CDC Control Task, CDC Source, CDC Splitter — were deprecated in February 2024, along with the Attunity connectors for Oracle and Teradata. Packages using them need a replacement path regardless of whether you migrate. Incremental Loads shows how to consume CDC through the SQL functions instead.
- Data Migration Assistant, which historically produced an SSIS assessment report, is deprecated. Treat any tooling recommendation you find in older material with suspicion and verify what is current before building an assessment process around it.
The deployment wizard’s own validation step, run against an Azure target, still flags package features that will not work there — a useful, current check that costs nothing.
Producing the inventory
Section titled “Producing the inventory”Much of the assessment is queryable from the catalog rather than by opening packages:
-- What exists, how often does it run, and does anyone care?SELECT f.name AS folder_name, p.name AS project_name, pk.name AS package_name, p.last_deployed_time, COUNT(e.execution_id) AS runs_90d, MAX(e.start_time) AS last_run, AVG(DATEDIFF(SECOND, e.start_time, e.end_time)) AS avg_secondsFROM catalog.packages AS pkJOIN catalog.projects AS p ON p.project_id = pk.project_idJOIN catalog.folders AS f ON f.folder_id = p.folder_idLEFT JOIN catalog.executions AS e ON e.package_name = pk.name AND e.project_name = p.name AND e.start_time > DATEADD(DAY, -90, SYSDATETIMEOFFSET()) AND e.end_time IS NOT NULLGROUP BY f.name, p.name, pk.name, p.last_deployed_timeORDER BY runs_90d DESC;Packages with runs_90d = 0 are retirement candidates. Packages with a very old last_deployed_time and high run counts are lift-and-shift candidates — stable and valuable. Packages deployed last week are redesign candidates, because the rewrite pays back through easier change.
Since .dtsx is XML, the file system can answer the rest:
# Which packages touch the local file system or use deprecated components?findstr /S /I /M "CDCControlTask" *.dtsxfindstr /S /I /M "Attunity" *.dtsxfindstr /S /I /M "\\\\" *.dtsx # UNC pathsfindstr /S /I /M "ScriptTask" *.dtsxCrude, but it converts “we should probably look at the packages” into a list with counts.
Option 1: keep it where it is
Section titled “Option 1: keep it where it is”The default, and frequently correct.
Keep SSIS on-premises when:
- Sources and targets are on-premises and staying there.
- The packages work, rarely change, and are understood by someone.
- SQL Server Agent and the existing operational routine are adequate.
- There is no strategic requirement forcing a move.
Migration has cost and risk, and “it is old” is not a business case. Where an estate genuinely needs improvement, the improvements that pay off most are usually not migration at all:
| Investment | Payoff |
|---|---|
| Source control + automated build and deploy | Kills configuration drift; makes changes safe |
| Parameterised configuration + SSISDB environments | One build runs everywhere |
| Structured logging + a batch audit table | Operations can answer questions without a developer |
| Idempotent loads | The recovery procedure becomes “run it again” |
| Splitting monolithic packages | Restart, test, and parallelise independently |
Those changes are cheaper than a migration, lower risk, and — importantly — they are prerequisites for a successful migration anyway. Doing them first is never wasted, whichever option you eventually choose. See DevOps and Automation.
Option 2: lift and shift to Azure-SSIS IR
Section titled “Option 2: lift and shift to Azure-SSIS IR”The Azure-SSIS Integration Runtime is a Microsoft-managed cluster of Azure VMs inside Azure Data Factory that runs SSIS packages natively. It exists specifically so packages can move to Azure without being rewritten.
What it gives you
Section titled “What it gives you”- SSISDB hosted on Azure SQL Database or SQL Managed Instance, with the familiar catalog structure — folders, projects, environments, and the same reports.
- The same tools: SSDT, SSMS, the deployment wizard,
dtutil. - Both deployment models: project deployment into SSISDB, and package deployment into a file system, Azure Files, or MSDB on a managed instance.
- Scale up (node size) and scale out (node count), and the ability to stop the IR when it is not needed — the main cost lever.
- Execution as a first-class pipeline step via the Execute SSIS Package activity, so SSIS becomes one activity in a wider cloud workflow.
What to check before assuming it is a drop-in
Section titled “What to check before assuming it is a drop-in”| Concern | Detail |
|---|---|
| File system access | Absolute paths and UNC shares do not exist on the IR. Repoint at Azure Files, Blob storage (via the Azure Feature Pack), or the node’s local %TEMP% |
| On-premises connectivity | Join the IR to a virtual network connected to your network, or proxy through a self-hosted IR |
| Third-party / custom components | Must be installed on IR nodes via a custom setup script. Licensing may not permit it |
| Windows authentication | Reaching on-premises resources with Windows auth needs explicit configuration |
| SSIS runtime version | The IR runs a Microsoft-managed SSIS version. Packages targeting a newer version will not run; lower-version packages may be upgraded at runtime. Check the current version before committing |
| Cost model | Billed for provisioned compute while running, not per package |
| Protection level | Password-based protection complicates deployment automation here too |
On cost specifically: a nightly batch that runs for 40 minutes should not leave the IR up for 24 hours. Starting and stopping the IR around the schedule is the difference between a sensible bill and an indefensible one, and it needs to be automated as part of the orchestration, not left to good intentions.
Where the IR can live
Section titled “Where the IR can live”The Azure-SSIS IR is provisioned in Azure Data Factory. For Synapse pipelines, Microsoft’s own documentation currently states two different things, and you should know both before planning:
| Source | What it says |
|---|---|
| Create an Azure-SSIS integration runtime | Marked applies to ADF and Azure Synapse Analytics; describes provisioning an Azure-SSIS IR “in Azure Data Factory (ADF) and Azure Synapse Pipelines”, with a note that some features are unavailable in Synapse |
| Integration runtime in Azure Data Factory | States “Synapse pipelines currently only support Azure or self-hosted integration runtimes”, and separately “Azure-SSIS integration runtimes aren’t currently supported in Synapse pipelines” |
That is a genuine, live contradiction between two current pages, not an artefact of stale writing on this site. The practical reading: provisioning is documented for Synapse and feature limitations are acknowledged, while the conceptual reference asserts non-support. ADF is the unambiguous, fully documented host.
If your target is Synapse, confirm current support with Microsoft before committing architecture — and prefer ADF where you have the choice, because it carries no such ambiguity.
When lift and shift is right
Section titled “When lift and shift is right”When you need to exit a data centre, the packages are numerous enough that rewriting is impractical, and the logic still earns its keep. It buys time; it does not modernise the design. Plan for that honestly — a lifted estate is the same estate, in a place that bills by the hour.
Option 3: re-orchestrate, keep the packages
Section titled “Option 3: re-orchestrate, keep the packages”A middle path that is easy to overlook: keep packages where they are, and change only what triggers them.
The mechanism matters here, and it is easy to state too loosely. A self-hosted IR is not a general-purpose runtime that executes on-premises SSIS packages on its own. Two distinct things exist:
| Pattern | What actually runs the package | Requires |
|---|---|---|
| Self-hosted IR as a proxy for an Azure-SSIS IR | The Azure-SSIS IR runs the package; the self-hosted IR handles supported on-premises access on its behalf | An Azure-SSIS IR and a self-hosted IR in the same data factory, plus ConnectByProxy on connection managers / ExecuteOnProxy on Execute SQL and Execute Process tasks. Has documented limitations |
| Triggering an existing on-premises execution | Your on-premises SSIS runtime, from the local SSISDB | A cloud orchestrator invoking something that starts a catalog execution — for example a stored procedure activity calling catalog.create_execution / catalog.start_execution against the on-premises instance over a supported connection |
Option 3 in this article means the second pattern: the packages and the runtime stay where they are, and only the trigger and the dependency graph move. The Execute SSIS Package activity is not that — it targets an Azure-SSIS IR.
The proxy pattern belongs to lift-and-shift (Option 2), where an Azure-SSIS IR already exists and you need it to reach on-premises data.
Worth doing when the pain is orchestration — cross-system dependencies, visibility, retries, alerting — rather than the ETL itself. It is dramatically cheaper than either of the other moves, and it is reversible.
Option 4: redesign
Section titled “Option 4: redesign”Rebuilding means expressing the same data movement in a different tool: a cloud data integration service, a warehouse’s own loading mechanism, or code.
Redesign when:
- The source or target has already moved to the cloud, so SSIS is a hop in the middle rather than the shortest path.
- The package is a thin wrapper over “copy this table” — cloud copy activities do that natively with less machinery.
- The transformation logic is already mostly SQL, which is portable.
- The package is actively developed, so the rewrite pays back through easier change.
- You need capabilities SSIS does not have: elastic scale, streaming, SaaS connectors, or a genuinely testable pipeline.
Do not redesign when the package is complex, stable, and rarely touched. That combination is exactly where a rewrite costs most and returns least.
What tends to be easy and hard to move
Section titled “What tends to be easy and hard to move”| Easy | Hard |
|---|---|
| Straight table-to-table copies | Deep Script Task / Script Component logic |
| Flat file loads into a table | Long chains of pipeline transformations |
| Logic already in stored procedures | Undocumented behaviour someone relies on |
| Watermark-based incremental loads | Third-party or custom components |
| Simple sequential control flows | Complex event handlers and checkpoint restart |
| Packages with parameterised configuration | Packages with hard-coded everything |
A staged approach usually beats a rewrite: move the loading first (a copy activity landing raw data), keep the transformation as SQL, and retire the package once both halves are proven. The SQL is the part that carries business meaning and the part that ports cleanly — so keeping it is both cheaper and safer.
Hybrid architectures
Section titled “Hybrid architectures”Real estates end up hybrid for years, and that is fine if it is deliberate. Common shapes:
| Shape | Description | Watch out for |
|---|---|---|
| Cloud orchestrator, on-prem SSIS | Pipelines trigger an existing on-premises catalog execution; the on-premises runtime still runs the package | The connectivity and credentials the trigger path depends on |
| Azure-SSIS IR alongside native cloud pipelines | Lifted packages and new cloud-native work coexist | Two operational models, two skill sets |
| On-prem extract, cloud transform | SSIS lands files in cloud storage; cloud tooling loads them | Data egress and landing-zone governance |
| Cloud extract, on-prem warehouse | Cloud sources pulled down for an existing warehouse | Latency, and re-creating cloud auth on-premises |
The failure mode is not the hybrid itself — it is having two of everything without deciding which is strategic: two logging systems, two alerting paths, two on-call runbooks. Pick which side is the destination and treat the other as transitional, with a date.
The platform-direction question
Section titled “The platform-direction question”Microsoft positions Data Factory in Microsoft Fabric as the next generation of Azure Data Factory, and its documentation now points new data integration work there while offering an upgrade path for existing ADF workloads.
The practical implication for an SSIS estate:
- If you are choosing a destination for a redesign now, understand where the platform is heading before standardising on something described as the previous generation.
- If you are lifting and shifting to buy time, this is less urgent — the Azure-SSIS IR remains the supported way to run SSIS packages in Azure.
Verify the current state of both products before committing a migration plan to paper. This part of the landscape moves considerably faster than SSIS itself does, and a plan written from a two-year-old blog post is a liability.
A realistic worked assessment
Section titled “A realistic worked assessment”A fictional estate of 140 packages across four folders. Triage produced this:
| Classification | Count | Reasoning | Action |
|---|---|---|---|
| Retire | 18 | Zero executions in 90 days; feed reports since replaced | Disable jobs, archive, delete after one quarter |
| Keep | 61 | On-premises source and target; stable; low change rate | Invest in CI/CD, logging, idempotence — no migration |
| Re-orchestrate | 22 | Stable packages, but dependencies span three schedulers | Move triggering to one orchestrator; packages unchanged |
| Lift and shift | 27 | Data-centre exit deadline; sources moving to Azure SQL MI | Azure-SSIS IR, fix file paths first |
| Redesign | 12 | Thin copy wrappers; targets already in the cloud | Replace with native copy + existing SQL transforms |
And the sequencing that followed:
MONTH 1–2 Fix portability blockers ON-PREMISES, for every package: • absolute paths and UNC references → parameters • hard-coded server names → parameters + environments • password protection levels → DontSaveSensitive • deprecated CDC components → SQL function equivalents (Every one of these was needed anyway.)
MONTH 2–3 Get the estate into a pipeline: source control, automated build, scripted environments, validation. Migrating an estate you cannot rebuild multiplies risk.
MONTH 3 Retire the 18. Confirm nothing breaks. This is the cheapest win.
MONTH 4 Lift the first 3 low-risk packages to Azure-SSIS IR. Run in PARALLEL with the originals. Reconcile on data.
MONTH 5–7 Lift the remaining 24 in waves, reconciling each wave. Automate IR start/stop around the schedule.
MONTH 6–9 Redesign the 12, one at a time, parallel-running each.
ONGOING Decommission deliberately. A package scheduled in BOTH places is a data-corruption incident waiting to happen.The order is the point. Blockers first, pipeline second, retirement third, migration last — because the first three reduce the risk and cost of the fourth, and are valuable even if the fourth is cancelled.
Internal behavior: what actually differs in the cloud
Section titled “Internal behavior: what actually differs in the cloud”Understanding why lifted packages break helps more than a checklist.
- The execution host changes. A package runs where the process is started. On the Azure-SSIS IR that is a managed node with its own file system, its own identity, and no line of sight to your network unless you provisioned one. Every “local path” assumption breaks here.
- The identity model changes. Windows authentication to on-premises resources requires deliberate configuration; managed identities and SQL authentication replace it elsewhere.
- The SSIS runtime version is Microsoft’s choice, not yours. Packages targeting a newer version will not run; older packages may be upgraded at runtime, which is generally transparent but occasionally is not.
- Component availability is what the node has. Built-in components and the Azure Feature Pack are provisioned; anything else requires a custom setup script at IR provisioning time.
- Compute is billed while provisioned, not while executing. The economics reward batching work into fewer, longer IR windows.
- SSISDB moves to a PaaS database, which changes backup, restore, and the catalog-password recovery story — but the catalog structure, views, and stored procedures are the same ones documented in Deployment, SSISDB, Environments, and Operations.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”Packages that shell out. Script Tasks invoking command-line tools, PowerShell, or local executables assume those exist on the host. On a managed node they do not, unless installed by custom setup.
Packages depending on drive mappings. A mapped drive exists only in an interactive session. These frequently already fail under a service account and are only working because someone runs them manually.
Encrypted packages with a lost password. EncryptAllWithPassword with no recorded password means the package cannot be built, deployed, or migrated. Discovering this during a migration is common and unpleasant.
Licence-bound third-party components. Some licences are node-locked or forbid cloud deployment. Check before assuming a custom setup script solves it.
Very chatty packages. A package making many small round trips to an on-premises source performs acceptably on the same LAN and badly across a network boundary. Latency, not throughput, is the constraint — and no amount of scaling the IR fixes it.
Estates with no documentation. The .dtsx XML is the documentation. Grep it. It is faster than interviewing people about packages nobody has opened in three years.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: migrate-everything
Section titled “Anti-pattern: migrate-everything”BAD: A migration programme exists → all 140 packages are in scope → 18 dead packages are carefully migrated and tested → 61 packages that had no reason to move acquire a new failure mode → the 12 that genuinely needed rebuilding are done last, in a rushBETTER: Triage first. Retire the dead. Leave the settled. Move what has a reason. Spend the saved effort on the 12 that matter.Anti-pattern: lift and shift without fixing paths
Section titled “Anti-pattern: lift and shift without fixing paths”BAD: Package works on-prem with \\fileserver\inbound\*.csv → deployed to Azure-SSIS IR → fails at validation on the first run → an emergency change to every affected package, under migration pressureBETTER: Parameterise every path BEFORE migrating, on-premises, where a mistake is cheap. Then the migration changes a parameter value, not a package.Anti-pattern: migration instead of engineering
Section titled “Anti-pattern: migration instead of engineering”BAD: "Our SSIS estate is a mess — no source control, no logging, manual deploys. Let's move it to the cloud." → the same mess, now billed hourly and with an unfamiliar failure mode.A badly managed SSIS estate becomes a badly managed cloud pipeline estate. The discipline has to come first, and it makes the migration easier if you still want it.
Other frequent mistakes
Section titled “Other frequent mistakes”- Rewriting stable, complex packages nobody was going to change anyway.
- Leaving the Azure-SSIS IR running continuously.
- Assuming third-party components will work on managed nodes.
- Migrating packages using deprecated components without a replacement plan.
- Skipping parallel running, so nobody can prove the new pipeline produces the same numbers.
- Never decommissioning, leaving a package scheduled in two places.
- Planning from stale documentation about which cloud service is current.
Failure modes, recovery, and troubleshooting
Section titled “Failure modes, recovery, and troubleshooting”| Symptom after migration | Likely cause | Action |
|---|---|---|
| Fails at validation on first cloud run | File paths, or an unavailable component | Run the deployment wizard’s Azure validation before migrating |
| Cannot reach an on-premises source | IR not joined to a virtual network | Join the IR to a VNet, or use a self-hosted IR proxy |
| Windows authentication failures | Auth model differs in the cloud | Configure Windows auth explicitly, or move to a managed identity |
| Package will not run: version error | Package targets a newer SSIS version than the IR | Check the IR’s runtime version; retarget the project |
| Custom component missing | Not installed via custom setup | Add a custom setup script at IR provisioning |
| Much slower than on-premises | Latency on chatty round trips | Restructure to fewer, larger operations; co-locate |
| Unexpected cost | IR left running | Automate start/stop around the schedule |
| Results differ from the original | A behaviour difference nobody characterised | This is why you parallel-run and reconcile on data |
Reconcile on data, not on success codes. Row counts, control totals, and spot comparisons between the old and new pipeline outputs — for several cycles, not one. A green run proves the package finished, not that it produced the same answer.
Performance and cost considerations
Section titled “Performance and cost considerations”- Provisioned compute is the cost model. Node size and count set the rate; uptime sets the bill. Batch work into fewer IR windows.
- Scaling out helps parallel package executions, not a single slow data flow. A badly shaped pipeline is just as slow on bigger hardware.
- Network boundaries change the physics. A design that was throughput-bound on a LAN can become latency-bound across a WAN, and no SSIS setting fixes that.
- Data egress has a price. Architectures that repeatedly move data across the boundary cost more than the compute.
- Co-location beats optimisation. Putting the compute next to the data is usually worth more than any package tuning.
Security and governance considerations
Section titled “Security and governance considerations”- Identity changes shape. Windows authentication and service accounts give way to managed identities and key vaults. Map every credential the estate uses before you move any of it.
- Network exposure changes. An IR joined to a virtual network is a new path into your network; scope it deliberately.
- Data residency and classification. Moving processing to the cloud can move data across jurisdictions — a compliance question, not an engineering one, and it needs answering before the first package moves.
- Secrets in packages become worse in the cloud, because the artifact travels further. Fix protection levels before migrating, not after.
- Catalog encryption and restore. SSISDB on a PaaS database has a different backup and recovery story; test the restore path before relying on it.
- Audit trails should survive the move. If execution history is a compliance artefact, plan how it is retained across the transition.
Testing, validation, and operational monitoring
Section titled “Testing, validation, and operational monitoring”- Parallel running is the test. Run old and new against the same input for several cycles and reconcile the outputs. Nothing else gives comparable confidence.
- Validate against the target platform before migrating — the deployment wizard’s Azure validation step is free and catches unsupported features.
- Test the failure paths in the new environment, not just the happy path. Quarantine, alerting, and retry all depend on infrastructure that has changed.
- Establish monitoring in the new environment before cutting over, not after. A migrated package that fails silently for a week is worse than not migrating.
- Track cost as an operational metric alongside duration and failure rate. It is the metric most likely to surprise you, and the one nobody is watching.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| Decision | Option A | Option B | Choose A when |
|---|---|---|---|
| Overall | Lift and shift | Redesign | Many packages, hard deadline, stable logic |
| Overall | Either | Keep + invest in CI/CD | Sources are staying on-premises |
| Connectivity | IR joined to a VNet | Self-hosted IR proxy | Sustained on-premises access is needed |
| Cost control | Stop/start the IR | Leave it running | The schedule is predictable and batched |
| Redesign scope | Move loading, keep SQL transforms | Full rewrite | The SQL already carries the business logic |
| Sequencing | Wave migration with parallel running | Big-bang cutover | You need to be able to reverse a wave |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”Q: A CTO says “we’re moving to the cloud, migrate all the SSIS”. How do you respond? Ask for the driver — data-centre exit, cost, capability, or strategy — because it changes the answer. Then triage rather than migrate uniformly: retire packages with no executions, leave packages whose sources and targets are staying on-premises, lift the ones with a real deadline, and redesign only the thin, actively developed ones. Migrating dead packages costs money and migrating settled ones adds risk for no benefit. The saved effort goes into the handful that genuinely need rebuilding.
Q: What breaks first when you lift a package to the Azure-SSIS IR?
File paths. Absolute local paths and UNC shares do not exist on a managed node, so anything reading D:\Inbound or \\fileserver\share fails at validation on the first run. Second is on-premises connectivity — the IR needs to be joined to a virtual network or proxied through a self-hosted IR. Third is components: third-party and custom components must be installed via a custom setup script, and licensing may not permit it.
Q: Why fix portability blockers before migrating rather than during?
Because on-premises they are cheap, reversible changes made under no time pressure, and every one of them — parameterised paths, parameterised server names, DontSaveSensitive protection levels, removing deprecated components — is worth doing whether or not the migration ever happens. Fixed during a migration, the same changes are made under deadline, in an unfamiliar environment, with a rollback that is also unfamiliar.
Q: How do you prove a migrated pipeline is correct? Parallel running with data reconciliation. Run old and new against the same input for several cycles, comparing row counts, control totals, and spot samples of actual values. A green execution proves the package finished, not that it produced the same answer — and behaviour differences (locale-dependent parsing, a different SSIS runtime version, a component upgraded at runtime) are exactly the kind of thing a success code hides.
Q: When is keeping SSIS on-premises the better engineering decision? When the sources and targets are staying on-premises, the packages are stable, and SQL Server Agent is adequate. Under those conditions a migration adds a network boundary, a new failure mode, a new cost centre, and a new skill requirement — in exchange for nothing. The money is better spent on source control, automated deployment, structured logging, and idempotent loads, which reduce actual incidents and are prerequisites for a migration anyway.
Q: What is the cost model of the Azure-SSIS IR and how does it change design? It bills for provisioned compute while the IR is running, not per package or per row. That rewards batching: consolidate work into fewer, longer IR windows and automate start/stop around the schedule. It also means a slow package costs money in a way it did not on owned hardware — so the performance work described in Performance Engineering has a direct financial return that it did not have before.
Q: An estate uses the SSIS CDC components throughout. Does that change your migration advice?
Yes, and it changes it even if you do not migrate. Those components — CDC Control Task, CDC Source, CDC Splitter — were deprecated in February 2024, so they need a replacement path on their own timeline. Since the replacement (consuming CDC through sys.fn_cdc_get_min_lsn/get_max_lsn and the net-changes functions from ordinary Execute SQL Tasks) is a package-level change, it is far cheaper to do on-premises first and migrate the result than to do both at once.
Q: How do you avoid a hybrid architecture becoming permanent by accident? Decide which side is strategic and put a date on the other. Hybrids fail not because they are hybrid but because nobody decides, so the organisation ends up maintaining two logging systems, two alerting paths, and two runbooks indefinitely. Name the destination, treat the other side as transitional, track how many workloads remain on it, and make that number visible.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Fundamentals
- SSIS Deployment, SSISDB, Environments, and Operations
- SSIS DevOps and Automation
- SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
- SSIS Large-Volume ETL and Performance Engineering
- Cloud comparison
Official Microsoft references
Section titled “Official Microsoft references”- Lift and shift SQL Server Integration Services workloads to the cloud
- Integration runtime in Azure Data Factory
- Create an Azure-SSIS integration runtime
- Migrate on-premises SSIS workloads to SSIS in ADF or Synapse Pipelines
- SSIS migration assessment rules
- Validate SSIS packages deployed to Azure
- Custom setup for the Azure-SSIS integration runtime
- Configure a self-hosted IR as a proxy for an Azure-SSIS IR
- Integration runtime in Azure Data Factory
- Run an SSIS package using the Execute SSIS Package activity