Most rewrite estimates for an SSIS package are wrong because they are made from the package name. Open the package, list the components, and the estimate falls out of the map below.
This is the map we use when a package has been classified as "rewrite" during an estate assessment. It covers Azure Data Factory; Microsoft Fabric pipelines use the same activity vocabulary for most of the control flow.
Control flow
| SSIS | Data Factory | Notes |
|---|---|---|
| Execute SQL Task | Script activity, or Stored Procedure activity | Script activity returns result sets; Stored Procedure activity does not. |
| Execute Package Task | Execute Pipeline activity | Parent/child parameter passing is explicit in ADF, not inherited. |
| Foreach Loop Container | ForEach activity | ADF's ForEach iterates an array from a Lookup or a parameter, not an enumerator object. |
| For Loop Container | Until activity | You maintain the counter in a pipeline variable yourself. |
| Sequence Container | Nothing | Grouping is visual in SSIS; in ADF you express order with activity dependencies. |
| Precedence constraints | Activity dependency conditions | Succeeded / Failed / Completed / Skipped. Expression-valued constraints become an If Condition. |
| File System Task | Copy activity, or Web/Script activity | Move and delete are not first-class; a Copy plus a delete-on-source setting covers most of it. |
| FTP Task | Copy activity with an FTP/SFTP linked service | Credentials move to the linked service and Key Vault. |
| Send Mail Task | Web activity to a Logic App, or an alert rule | There is no mail activity. Most estates are better served by an alert than by a mail step. |
| Script Task | Azure Function activity, or nothing | See below. This is the decision point. |
Data flow
The data flow is where the map stops being a table.
A data flow that reads a source, applies a Derived Column or two, and writes a destination is a Copy activity, sometimes with a query on the source side doing the transformation. That is the majority of packages in most estates, and it is the cheapest rewrite you will do.
A data flow with Lookup, Merge Join, Conditional Split, Aggregate, Union All or a Slowly Changing Dimension transformation is a Mapping Data Flow — or, more often and more cheaply, a stored procedure that the pipeline calls. If both the source and the destination are already in the same SQL engine, pushing the transformation into SQL is usually less code and faster than either option.
Three data-flow components have no equivalent and need a decision rather than a translation:
- Script Component. Row-by-row .NET in the pipe. There is nothing like it. Read what it actually does before costing it: many are a string cleanup that becomes a SQL expression, and the rest are a real algorithm that becomes an Azure Function or stays on SSIS.
- Slowly Changing Dimension transformation. The SSIS wizard output is notoriously slow and nobody enjoys maintaining it. A MERGE statement, or a Mapping Data Flow with an Alter Row transformation, replaces it. Either way you are rewriting the logic, not porting it.
- Third-party components. A commercial CDC, Salesforce, SAP or compression component has no ADF counterpart. Either the vendor has an ADF connector, ADF has a native connector, or the package stays on the Azure-SSIS integration runtime with the component installed by a custom setup.
The four things with no equivalent
Beyond components, four SSIS mechanisms have no ADF analogue at all. Each one is a design decision, and each one is where naive rewrites run over:
- Package configurations and project parameters. ADF has pipeline parameters and global parameters, plus Key Vault for secrets. There is no environment-reference concept: you rebuild it with parameters and a deployment pipeline.
- Protection levels. Sensitive values in SSIS are encrypted by a user key
or a package password. In ADF, secrets live in a linked service backed by
Key Vault. Every credential in every connection manager has to be located
and re-homed, and
EncryptSensitiveWithUserKeypackages will not even open for the person doing the work. - Checkpoints and transactions. SSIS checkpoint files and MSDTC transactions restart a package part-way. ADF has retry policies on activities and rerun-from-failed-activity, which is not the same thing. If a package genuinely depends on a distributed transaction, that is an argument for leaving it where it is.
- Logging. SSIS log providers and the SSISDB execution reports are
replaced by Azure Monitor and the Data Factory run history. The queries your
team runs today against
catalog.executionsdo not survive; budget for rebuilding whatever operational reporting depends on them.
How to use the map
Run it as an inventory, not as a reading exercise. Parse the .dtsx XML for
component type names, count them across the estate, and you will usually find
that a handful of component types account for most packages. Estimate those
carefully and the rest follows.
That inventory is the first half of an estate assessment. If you would rather not spend two weeks building the parser, tell us what the estate looks like and we will run it.