A migration proposal written from a slide deck has one answer for the whole estate. An assessment written from the packages has three, and the split is almost never the one that was assumed going in: "retire" is consistently larger than teams expect, and "rewrite" consistently smaller.
Here are the questions, in order. Stop at the first one that answers.
1. Does anything consume the output?
Ask of the destination table, not the package: when did anything last read it? Query the plan cache and the Query Store for references, ask the BI team which reports are on it, and check whether the report itself has been opened this year.
A surprising share of any long-lived estate is loading tables nobody reads — usually because the report it fed was replaced and the load was never switched off. Retire. This is the cheapest work in the project and it should be done first, because every retired package is one you do not have to assess, migrate, test or reconcile.
Retire means disable the job, keep the package, and wait a full business cycle before deleting anything. Month-end finds the consumers that a month of quiet did not.
2. Has it run successfully in the last year?
Check catalog.executions and msdb.dbo.sysjobhistory. Packages that have
been failing since a server move, or that have not been scheduled at all,
belong in the same conversation as question 1 — with one caveat: annual and
quarterly packages are real, and a package that runs once a year at audit time
is load-bearing precisely because nobody thinks about it. Look at the schedule
before you conclude anything from the run history.
3. Does it have a blocker?
Four things stop a package from moving as-is:
- A third-party or custom component with no equivalent on the target and no vendor path.
- A 32-bit-only provider — most commonly the Access Database Engine driver reading an Excel or Access source.
- An unavailable data source: an on-premises file share, a mainframe extract, a database with no network path from the new location.
EncryptSensitiveWithUserKeywith the original user gone, so the package will not open with its credentials intact.
None of these is fatal. A custom setup on the Azure-SSIS integration runtime installs components and drivers; a self-hosted integration runtime configured as a proxy reaches on-premises data; a protection-level problem is solved by re-entering the credentials once. But each one is work, and each one has to be counted before a date is promised. This is where assessment tooling helps and then stops: it will tell you the package has a blocker, and it will not resolve it.
4. Is it failing, slow, or changing often?
Now the real question. A package that runs in four minutes, has not been edited since 2019, and has never woken anyone up does not need rewriting — no matter how ugly the data flow is. Lift and shift it. The Azure-SSIS integration runtime has been generally available for years, SSIS still ships inside SQL Server, and there is no deadline forcing an ETL rewrite. A rewrite that adds no capability adds only risk.
Rewrite the packages that earn it:
- it fails often enough to be on someone's calendar;
- it is on the critical path of the nightly batch and it is the long pole;
- it changes every quarter, so the maintenance cost is real and recurring;
- it does something the target platform does much better — an incremental load that should be a tumbling window, a Foreach over a folder that should be an event trigger, a Slowly Changing Dimension transformation that should be a MERGE.
In most estates that is a minority of packages carrying a majority of the pain.
5. What does it cost to verify?
The last question, and the one that changes answers. A package whose output can be reconciled with a row count and two sums is cheap to rewrite. A package that feeds a regulatory submission, where "close enough" is not a category, is expensive to rewrite and should be moved as-is unless it is genuinely broken.
Verification cost belongs in the estimate next to build cost. It is frequently the larger number, and leaving it out is why rewrite-everything plans run long.
What the answer usually looks like
A mid-market estate assessed this way tends to come out somewhere near: a meaningful slice retired outright, the clear majority lifted and shifted, and a small set rewritten — chosen because they hurt, not because they are old.
That is a less exciting plan than a platform rewrite. It is also the one that finishes, and it leaves budget for the packages that actually needed the work.
Doing this yourself
You can. The inputs are catalog.executions, msdb.dbo.sysjobhistory, the
.dtsx files, and a fortnight of someone senior's attention. The output is a
spreadsheet with one row per package and a defensible answer in the last column.
If that fortnight is not available — and in a team of two BI developers it usually is not — an SSIS estate assessment is exactly this checklist, run across the whole estate, with the risk register and the sequenced plan at the end of it. Tell us what you are running.