Migrating SSIS packages to Apache NiFi: what the move involves
NiFi replaces the file, API and load pipeline work in SSIS, not the database engine. If your SQL Server estate runs packages that copy files, download feeds, call REST endpoints and load tables, those flows can move to Apache NiFi, while the SQL Server instances, schemas, stored procedures and reporting layer stay where they are. The effort depends less on how many packages you have than on how much of each package is custom script logic, such as Script Tasks, Script Components and embedded expressions that carry business rules. Packages that are mostly configuration around standard tasks are usually quicker to rebuild than packages that hide logic in code. Microsoft describes SQL Server Integration Services as a platform for enterprise data integration and transformation, with work such as copying or downloading files, loading data warehouses and managing SQL Server objects. Its packages are stored in an SSIS Catalog database, so the catalog holds the deployed projects, environments, parameters and execution history that your operations team depends on. NiFi has no SSIS importer. Each package is a rebuild, and the practical starting point is an inventory of every package, its connection managers, its schedule in SQL Server Agent or an external scheduler, and the parameters it reads from environments. The Microsoft Learn overview of SSIS is a useful reference for confirming which task types your packages use. Before committing to a plan, confirm three things with your own team rather than assuming them: which packages still run in production, which ones depend on SSIS-specific behavior such as transactional scope or package-level event handlers, and which data transformations must stay in SQL because they are set-based and already fast there. Ask a partner for a written scope that lists each package, its target flow or SQL object, and the owner who will sign off the output. That list, not a package count, is what sets the effort and the test plan.
Which SSIS components map to NiFi and which do not
This table compares common SSIS control flow and data flow components with their closest NiFi counterparts and marks the ones that need redesign or stay outside NiFi. Read each row as a starting point for a design decision, then confirm processor names and property sets against the NiFi 2 component documentation for your release.
| SSIS component | NiFi equivalent | Migration notes |
|---|---|---|
| File copy and download tasks | ListFile or GetFile, FetchFile, PutFile, and protocol-specific processors such as FTP or SFTP processors | Replace polling with listing where possible. Confirm the processor set for your source and target protocols in the component documentation. |
| Web service and API calls | InvokeHTTP with controller services for SSL and authentication | Map SSIS script or web service tasks to InvokeHTTP. Pagination and token refresh usually need explicit flow logic, not a single processor setting. |
| OLE DB source and destination | ExecuteSQL, QueryDatabaseTable, PutDatabaseRecord | Use query-based readers for ad hoc extracts and incremental readers where a maximum value column exists. Set-based SQL should stay in the database. |
| Connection managers | Database Connection Pooling Service and other controller services | Each shared connection manager becomes one controller service referenced by several processors. Store credentials in sensitive property values, not in flow definitions. |
| Package parameters and project parameters | Parameter contexts, with sensitive parameters and parameter providers | Group parameters by environment. Confirm how your team will supply values per environment before moving packages. |
| SQL Server Agent or SSIS Catalog schedules | Run schedules on processors (timer or cron driven) and external schedulers | Scheduling moves to the flow. Keep job dependencies that span systems in an orchestrator if NiFi cannot express them cleanly. |
| Derived Column with expressions | UpdateRecord, QueryRecord or UpdateAttribute with NiFi Expression Language | Simple expressions translate directly. Complex nested expressions and type casting need testing against sample data on both sides. |
| Script Task and Script Component | ExecuteScript, ExecuteGroovyScript or a custom processor | Scripts that call .NET libraries or COM objects do not port directly. Plan a rewrite in a supported scripting language or a custom processor. |
| Fuzzy Lookup, Fuzzy Grouping and Data Mining | No direct equivalent, usually redesigned or kept outside NiFi | Matching logic may move to a dedicated service or a database function. Validate match quality on your own data before cutover. |
| Set-based transformations and stored procedures | Invoke from NiFi, do not rebuild in flow | Keep joins, aggregations and bulk merges in SQL Server. NiFi triggers the procedure and handles the handoff, errors and retries. |
Documented paths for moving SSIS work into NiFi
Before any flow is designed, you need a complete list of what the SSIS estate does. SSIS packages are stored either as .dtsx XML files in source control or file shares, or as deployed projects inside the SSIS Catalog database, which Microsoft describes as the store for packages deployed to the server. Reading both sources gives you the connection managers, data flow tasks, execution schedules and parameters that each package depends on. Microsoft describes SSIS as a platform for copying or downloading files, loading data warehouses, cleansing data and managing SQL Server objects, so the inventory should sort packages by those job types.
- Package XML: Parse the
.dtsxfiles to list tasks, connection managers, variables and parameters per package. - Catalog metadata: Query the SSIS Catalog for deployed projects, environments and execution history to see what actually runs.
- Schedules: Record which SQL Server Agent jobs or external schedulers start each package, since those triggers move separately.
- Dependencies: Note which packages call other packages, share tables or depend on files written by an earlier step.
- Retired work: Mark packages with no recent executions so they are not rebuilt.
Check the inventory against a sample of recent execution logs before you rely on it, because packages in source control do not always match what is deployed.
NiFi stores flows as versioned process groups. Through a Flow Registry Client, a process group can be committed to a Git repository, and NiFi 2 includes clients for GitHub and GitLab. Those versioned flows become the reusable definitions you move between environments, and the flow configuration file on the NiFi side is flow.json.gz on NiFi 2, which replaces the flow.xml.gz format used by earlier versions. The NiFi migration guidance requires moving through NiFi 1.27.0 before 2.0.0 when you bring an existing 1.x flow across.
- Registry client: Configure the GitHub or GitLab Flow Registry Client with a repository that your team controls.
- Versioned flows: Commit each process group to the repository, with a clear path per SSIS project.
- Import: Import the versioned flow into the target NiFi 2 instance, then review every processor for missing properties.
- Flow file handling: Treat
flow.json.gzas a NiFi-managed file, and do not edit it by hand. - Version checks: Confirm the export and import steps for your exact NiFi version in the administration and component documentation.
Record the commit and version used for each deployed flow so that rollback is a known operation.
SSIS handles environment-specific values through project parameters and environments in the SSIS Catalog. NiFi uses parameter contexts for the same purpose: connection strings, host names, file paths and credentials are referenced by name in processors, and each environment supplies its own values. Flows committed to a registry keep the parameter names, so the values can differ between development, test and production without changing the flow. Sensitive values need a sensitive parameter type and should come from a source your secrets process controls, not from the flow definition.
- Parameter inventory: List every SSIS project parameter and environment variable, then map each to a NiFi parameter context.
- Sensitive values: Mark passwords and tokens as sensitive and keep them out of the versioned flow.
- Per-environment contexts: Create one parameter context per environment, with the same parameter names.
- Connection references: Replace SSIS connection managers with controller services that read from those parameters.
- Review step: Check that no environment value is hard-coded in a processor property.
Confirm in the NiFi administration documentation how parameter providers and sensitive values are handled in your version.
Several SSIS concepts have no direct NiFi equivalent, and the migration should name them early. The SSIS Catalog holds execution logs, package versions and environment definitions in SQL Server tables, and those records stay in SQL Server unless you deliberately copy them. Logic written in Script Tasks or Script Components is C# or VB code that must be rewritten, usually as a scripted or custom component, rather than translated automatically. Database-side work such as stored procedures, set-based transformations and indexing usually remains in SQL Server, and NiFi should call it rather than replace it.
- Catalog history: Decide whether execution history is archived, exported or retired.
- Script logic: Rewrite script tasks and components as NiFi processors or scripts, and test them against the original output.
- Set-based SQL: Keep stored procedures and heavy transformations in the database where they already run.
- Schedule ownership: Move each schedule to NiFi run schedules only after the owning team agrees.
- Unsupported components: List third-party SSIS components, since each needs its own replacement plan.
Confirm your decisions with the team that owns the SQL Server estate and document them in the inventory.
How a parallel run and cutover are sequenced
The sequence moves SSIS packages into NiFi in controlled groups, proving each flow produces the same results as its SSIS original before the old job is switched off. Each step ends with a written check, so the cutover rests on evidence rather than assumption.
Inventory packages by source, destination and schedule
Your team lists every package in the SSIS Catalog, along with its sources, destinations, connection managers, variables, schedule and the SQL Server Agent job that triggers it. Each package is then classified as move, redesign or keep in SQL. The output is a signed inventory with one owner per package and a named decision for each. Packages with no known consumer or owner are flagged for confirmation before any build work starts, since retiring them is a cheaper decision than migrating them.
Group packages into migration waves
The inventory is sorted into waves that share a pattern, such as file copies, API pulls and database loads, and that share downstream dependencies. Packages that feed the same warehouse tables or reports go in the same wave so that reconciliation can be done for the whole set at once. Each wave has a stated owner on both the data side and the business side. The output is an approved wave plan that records which packages move in each wave and which SSIS jobs stay active until their wave is cut over.
Build one pilot flow per pattern
Your team and DeployOpen build one NiFi flow each for the file, API and load patterns, using a representative package from the inventory. Each pilot maps data flow components to record-based processors, moves connection managers into controller services, and sets run schedules to match the original trigger. Credentials are placed in sensitive parameters rather than flow files. The pilot is accepted when a test run against a non-production copy of the source produces output that matches the SSIS result, and the team agrees that the pattern can be reused for the remaining packages.
Run NiFi and SSIS in parallel
For each package in the first wave, the SSIS job and the NiFi flow run on the same schedule against the same sources, but the NiFi output is written to a staging target that analysts do not use for decisions. Provenance records in NiFi allow the team to trace individual files or records through each processor, which helps explain any difference found later. Both runs are kept for an agreed period. The output is a set of paired runs with known start and end times that can be compared.
Reconcile row counts and checksums
After each parallel run, a reconciliation job compares row counts, column-level checksums and key totals between the SSIS output and the NiFi output for the same load window. Differences are traced to a specific package, source file or record using provenance, then either fixed in the flow or accepted and documented as an intended change. The wave is ready for cutover only when the reconciliation report shows no unexplained differences and the data owner signs off on the accepted changes. The signed report becomes part of the change record.
Cut over one package group at a time
Once a wave passes reconciliation, its SSIS jobs are disabled in SQL Server Agent and its NiFi flows are pointed at the production targets. The cutover is done for one group at a time, with the rollback step written down before the switch: re-enabling the SSIS jobs and reverting the staging target. Downstream consumers are told when their feed changes. The output is a cutover record showing which jobs were disabled, which flows went live and the first successful production run for each.
Decommission SSIS jobs after a stable window
After each migrated group has run in production through an agreed stable window, the team removes the SSIS packages from the SSIS Catalog and deletes the corresponding Agent jobs, with the package definitions exported and archived first. Packages marked as keep in SQL stay where they are, and their dependencies are documented. The step is complete when the catalog no longer holds migrated packages, the archive is verified and the provenance and reconciliation records are retained according to your data retention policy.
What drives the cost and duration of the move
This table lists the factors that change how much effort an SSIS to NiFi move takes and who pays for each part of it. Read it as a checklist for the inventory you should complete before asking any partner, including your own team, for a quote. The main takeaway is that package count matters less than logic hidden in Script Tasks, data flows and parameters, and that reconciliation and parallel-run terms should be fixed in writing before work starts.
| Driver | Why it changes effort | How to keep it down |
|---|---|---|
| Package count and grouping | Each package needs mapping, rebuilding, testing and a cutover decision, so effort grows with the number of distinct flows, not only files. | Group packages by source, target and schedule, and retire packages that no longer run. |
| Script Tasks and Script Components | Custom C# or VB code has no direct NiFi equivalent and usually needs a rewrite as a processor, script or external service. | Inventory every script, classify it as file handling, API call or business rule, and move only what still earns its place. |
| Logic inside data flows | Lookups, merges, derived columns and conditional splits must be redesigned as record processing and tested for the same results. | Document transformation rules and their expected outputs before rebuilding, so testing has a fixed baseline. |
| Connection managers and credentials | Each connection needs a controller service, a secret source and a rotation plan, and shared credentials multiply the review work. | Count unique connections and credentials first, then decide which secrets move to an external store. |
| SSIS Catalog environments and parameters | Environment-specific values and project parameters must map to NiFi parameter contexts and parameter providers across dev, test and production. | Export the catalog configuration per environment and agree the parameter naming before the first flow is built. |
| Reconciliation requirements | Row counts, checksums and financial totals must match between SSIS output and NiFi output, and matching rules decide how long testing runs. | Agree reconciliation criteria per package group in writing, including tolerances and sign-off owners. |
| Parallel-run length | Running SSIS and NiFi side by side doubles load and monitoring until the business accepts the results. | Set the parallel-run period and exit criteria in the statement of work rather than leaving them open-ended. |
| Target platform decision | A self-hosted cluster, Kubernetes or virtual machines each need different storage, identity, TLS and upgrade work, and an undecided target adds rework. | Decide the platform and NiFi 2 version before scoping, and confirm the Java 21 runtime is available. |
| Recurring NiFi operations after cutover | Monitoring, patching, certificate renewal, NiFi 2.x upgrades and flow changes continue after go-live, unlike the one-time rebuild. | Ask a quote for both the one-time migration and a separate support or retainer estimate, and confirm who owns flow changes. |
What usually goes wrong in SSIS to NiFi migrations
Most failed SSIS to NiFi migrations do not fail on day one. They fail quietly through type changes, hidden code, leaked credentials, lost restart behavior, duplicate loads and infrastructure work that arrives too late. The cards below cover the risks we see most often and the checks that catch each one before cutover.
Silent type and null differences
SSIS data flows enforce metadata types and code pages at the pipe, while NiFi records carry schema-defined types that are converted by record readers and writers. A decimal stored as a string, a date parsed in a different locale or an empty string treated as NULL can all pass a row count check and still corrupt the target. Compare column types, precision and null handling on a sample of real rows from both pipelines before any load runs. Write those comparisons into the test plan so they are repeated.
Logic hidden in Script Tasks
Script Tasks and Script Components often hold business rules, lookups and file handling that nobody has documented outside the package. These pieces do not map to a processor, so they are easy to drop during conversion. Export every script, list what each one reads, writes and decides, and assign an owner to confirm the behavior. Treat any script without a confirmed owner as an open migration item rather than a detail to finish later.
Credentials carried over from packages
SSIS packages often store connection passwords in package configurations, environment variables or the SSIS Catalog with sensitive parameters. Copying those values into NiFi flow files or parameter contexts moves the exposure into version control. Define NiFi parameter contexts or a secrets source for every credential before building flows, mark sensitive values as sensitive, and confirm that exported flow definitions contain no secret values.
Lost restartability and checkpoints
SSIS checkpoints let a failed package resume from a known task, and many teams rely on that without writing it down. NiFi does not replicate checkpoint behavior by default, so a failed run may restart from the source and reprocess data. Decide which pipelines need resumable state, store watermarks or processed-file markers in a database table the flow reads, and test a forced failure in the middle of a run. Ask your team which failure modes they currently recover from by hand.
Duplicate loads during parallel running
Running SSIS and NiFi side by side is the safest cutover approach, but both pipelines may pick up the same source files or query the same change window. The result is duplicate rows in the target or double-counted totals in reports. Assign each source to one active pipeline per period, use unique keys or merge logic on the target, and compare outputs by key rather than by row count alone.
Cluster and security set up after flows
Teams often build the flows first and add TLS, authentication, cluster coordination and certificates at the end. NiFi binds to 127.0.0.1 when no security configuration is provided, so the instance is unreachable until those settings exist, and changing identity or cluster settings later can force flow rework. Choose the authentication provider, certificate approach and coordination method (ZooKeeper or Kubernetes) before the first production flow is designed, and confirm them in the administration guide.
What should stay in SQL Server after the move
Some of what an SSIS package does is better left in the database. Set-based transformations, stored procedures, and warehouse merge logic (for example, MERGE statements that upsert dimension and fact tables) are work the SQL Server engine already executes efficiently inside the database, close to the data. Re-creating that logic as record-by-record processing in NiFi usually moves the work out of the engine and into the flow, which tends to be slower and harder to test. The default position for this work should be to keep it in SQL Server and have NiFi orchestrate it, unless the inventory shows a specific reason to move it.
NiFi can call or load into these objects without taking over their logic. A flow can trigger a stored procedure through a database connection pool controller service, load staging tables with a record writer, and then invoke the merge procedure once the load completes, with the outcome routed by success or failure relationships. Decide the boundary per package during the inventory, not by project-wide rule. For each package, record whether its transformations are row-oriented and easy to express as NiFi record processing, or set-based and already tuned in T-SQL. Packages that mix both often split cleanly at the staging table.
Keep audit needs separate from the migration design. The SSIS Catalog records execution history, messages and parameter values for packages it runs, while NiFi provenance records what happened to each FlowFile as it moved through the flow. These answer different questions, and neither replaces the other. Confirm with your compliance team which execution records, retention periods and reviewer access they require for each data set, and whether SQL Server tables that hold run history must continue to be written after cutover. Agree that answer before deciding what the SSIS Catalog logging and NiFi provenance each need to store.
Questions buyers ask before migrating SSIS to NiFi
Can Apache NiFi import SSIS packages directly?
No. The project does not document an importer that reads SSIS packages (.dtsx files) and produces NiFi flows, so each package is rebuilt by pattern. A file copy becomes a listing and fetch flow, an API call becomes an InvokeHTTP-based flow, and a database load becomes a query or put processor backed by a controller service. Packages built from a few repeated patterns move quickly once the first pattern is proven, while packages with heavy custom script tasks or nested logic need redesign rather than translation. Before any build starts, confirm the inventory of task types with your team, because that inventory determines how much of the work is rebuilding and how much is redesign.
How is the effort for an SSIS to NiFi migration estimated, and what drives it?
Estimate from the package inventory, not from the number of packages alone. The main drivers are the count of distinct task and data flow patterns, the number of source and target systems, how much logic lives in script tasks or stored procedures, error handling and restartability requirements, and the number of environments that must be reproduced. Connection managers, parameters and the SSIS Catalog configuration each add work because they must be mapped to NiFi controller services, parameters and deployment settings. Ask any provider for a quote built from a written inventory with a per-pattern breakdown, and ask which items are assumed out of scope so you can see what is excluded.
How long should a parallel run last, and who decides when it ends?
The length of a parallel run depends on how often your packages run and which business cycles they cover. A package that runs nightly needs enough nights to cover month-end, load peaks and any exception paths, so the window should be set against your own calendar rather than a generic number. Agree the exit criteria in writing before the run starts: row counts, reconciliation totals, error rates and sign-off from the data owners. The business owner of each data set should approve cutover per flow, and the security or compliance owner should confirm that any new credentials and logs meet your policies. Confirm this decision model with the implementation partner at scoping.
What must our team provide before the migration starts?
The migration depends on access and information that only your team can supply. Provide a complete package inventory with the task types, schedules, owners and dependencies, the SSIS Catalog environments and parameter values, the source and target connection details, and a list of the credentials each package uses. Give the implementation team read access to the SSIS Catalog or exported project files, sample and representative test data that can be used outside production, and named contacts for each data set who can confirm expected results. Ask for a written list of what you will provide and by when, since missing inputs are one of the most common causes of schedule slip.
Can our internal team migrate SSIS packages to NiFi without outside help?
Yes, if your team has NiFi operating experience, data engineering skills for the source and target systems, and capacity for testing and cutover. Many teams that run NiFi already can rebuild a small set of packages in-house. The harder parts are usually the first pattern decisions, controller service design, secure configuration, clustering and reconciliation against SSIS output. Compare the cost of your own staff time, including the parallel run and the knowledge that remains after the project, against a fixed-scope engagement. DeployOpen plans, implements and documents self-hosted open-source platforms in infrastructure you control, so a scoped engagement can cover the build while your team takes over operations.
What stays in SQL Server after the migration?
Usually the database engine, its stored procedures, and the schemas and tables that applications query stay in SQL Server. NiFi takes over file movement, API calls, scheduling and orchestration of loads, but set-based transformation that already runs well inside the database is often better kept there than rebuilt as record-level processing. The SSIS Catalog itself is retired with the packages, so its logs, environment settings and execution history need an archive plan. Decide during the inventory which logic moves into NiFi, which stays as stored procedures, and which is retired, and record that decision per flow.
Is Apache NiFi licensed for paid use, and what does that mean for our costs?
Apache NiFi is an Apache Software Foundation project released under the Apache License 2.0 (SPDX: Apache-2.0), and the project itself offers no paid edition. Your licensing cost is therefore zero for the software, but you still pay for infrastructure, staff time, implementation services, support or retainers if you choose them, and any third-party components or connectors your flows use. Check the license of each dependency and any commercial service you connect to, because those terms are separate. The NiFi downloads page also notes the support status of each release line, so plan upgrades as part of the total cost.
How DeployOpen scopes and delivers the SSIS to NiFi move
DeployOpen is a Chainscore Labs company that plans, implements, integrates, hardens and supports self-hosted open-source platforms inside your own infrastructure. The cards below follow the order in which an SSIS to NiFi move is usually scoped, proven and then run.
Fixed scope agreed in writing
Most engagements are fixed-scope, so the package list, deliverables, environments and timelines are agreed in writing before any flow is built. For an SSIS move, the written scope should name which packages move, which stay in SQL Server, and which are retired. Ask for the scope document to list every excluded item, since those exclusions drive most later disputes over cost.
Package inventory and risk ranking
Before any build, the SSIS catalog is read package by package to record sources, targets, connection managers, parameters, schedules and custom or third-party components. Each package is then ranked by data risk, dependency count and business impact. The inventory becomes the basis for the quote, so ask to see it before you approve the scope.
Pattern pilots before full build
A small set of representative packages, such as one file copy, one API pull and one database load, is converted first as a pilot. The pilot confirms how data flow tasks map to NiFi record processors and how connection managers become controller services. Agree on the pilot acceptance criteria in the scope so the full build starts from proven patterns.
Reconciliation and cutover support
Each migrated flow is reconciled against its SSIS output with row counts, checksums and sampled field comparisons, and the parallel run period is planned with you. Cutover support covers schedule switch-over, disabling old SSIS jobs and a rollback path. Confirm who owns sign-off on reconciliation results, since that decision sits with your data owners, not the migration team.
Hardening and handover documentation
Flows are deployed with TLS, a chosen authentication provider, and sensitive values kept out of flow definitions. Handover includes runbooks, flow documentation and the decisions behind each mapping, so your team can operate NiFi without the migration team. DeployOpen prepares evidence for your compliance reviews, but independent auditors and certification bodies issue any audit report or certification.
Retainers for support and upgrades
After cutover, ongoing support, NiFi version upgrades and periodic security testing can be covered by a retainer rather than a new project. This matters for NiFi because 1.x is out of maintenance and version 2 changes flow configuration files and some components. Ask what the retainer covers, how upgrade work is scoped, and how response expectations are written down.
