Skip to main content

SSIS importer

Coming soon

The SSIS importer is about to be released as open source (Apache License 2.0). Until then, we run it for you: contact us.

The SSIS importer converts SQL Server Integration Services packages (.dtsx) into Apache Hop workflows and pipelines, and writes a migration report that lists every component that needs a person, with the reason. It will be open source (Apache License 2.0), and runs from the Hop GUI, from hop-import, or as a standalone command line.

PartWhat it doesWhere it runs
Hop pluginAdds File → Import from SSIS… to Hop GUI and -t ssis to hop-importInside Apache Hop 2.19.0 or later
Command lineThe same import as a single jar, for scripts and CIAny machine with Java 21
Migration reportssis-migration-report.md in the output folderWritten by every import

The plugin and the command line run the same code, with the same options and defaults, so they convert, skip and report identically. Nothing in the import needs Windows, SQL Server or SSIS.

How it works​

  1. The importer walks the input folder for .dtsx packages. Every folder that holds packages is read as one SSIS project, together with the project connection managers (*.conmgr) and the project parameters (Project.params) in it. Packages in obj/ folders are skipped: they are the copies SQL Server Data Tools makes when it builds a project.
  2. Each package's control flow becomes a Hop workflow. Sequence, For Loop and Foreach Loop containers become workflows of their own, run from the package's workflow.
  3. Each Data Flow Task becomes a Hop pipeline. For each component, the converter registered for its class writes the Hop transforms. A component with no converter, or whose converter fails, becomes a placeholder.
  4. SSIS expressions (in Derived Columns, Conditional Splits, precedence constraints, variables and Expression Tasks) are parsed and translated into Java that keeps SSIS's rules.
  5. Database connection managers become Hop relational connections; project parameters become a Hop environment configuration file.
  6. The report is written from what the import actually did.

What you get​

For a project folder Sales with a package LoadOrders.dtsx, in the output folder:

  • Sales/LoadOrders.hwf: the package's control flow, the workflow you run;
  • Sales/LoadOrders/<Data Flow Task>.hpl: one pipeline per Data Flow Task;
  • Sales/LoadOrders/<Container>.hwf: one workflow per Sequence, For Loop or Foreach Loop container, and helper pipelines where a task needs one (an Execute SQL Task with a single-row result, an Expression Task, a Foreach Loop's item list);
  • .cs.txt or .vb.txt files next to the workflow or pipeline: the source of each Script Task and Script Component, kept for porting;
  • metadata/rdbms/: one Hop connection per database connection manager;
  • environments/sales-config.json: the project parameters, when the project has any;
  • ssis-migration-report.md: see The migration report.

When the input folder is itself the project folder, the package workflows are written at the top of the output folder.

Design choices worth knowing​

  • Topology is kept. A component or task the importer cannot convert stays in the pipeline or workflow as a Dummy with its name and hops, so nothing disappears silently.
  • SSIS's rules, not Hop's. Expressions run in User Defined Java Classes with a small runtime that keeps SSIS's semantics: NULL propagates, && and || use three-valued logic, casts such as (DT_WSTR, 50) convert as SSIS does, and functions such as SUBSTRING, TOKEN and DATEDIFF count as SSIS counts.
  • The same data gives the same tables. Converted packages are checked against SSIS itself: public sample projects and synthetic ones are run with SSIS on Linux and, converted, with Hop, from the same data, and every table they load is compared row by row. Seven projects load the same tables on both sides, apart from three known differences: a package that races its own stored procedure, a Merge Join after a case-insensitive Sort, whose result in SSIS depends on row order, and a business key that comes twice in one load, whose result in SSIS depends on its buffers. The report flags the last two. Every other difference those runs found was fixed in the importer: how empty values in delimited files are read, money rounding, NULL in a Conditional Split, duplicate lookup keys, OLE DB Command parameter order, and how keys are matched.
  • Parallel where SSIS is parallel. SSIS runs tasks that do not depend on each other at the same time; the workflows do the same. Several tasks that one task waits for run in a workflow of their own, which the waiting task follows on success. Hop's Join action is only used for graphs that do not have that shape, and the report flags those.
  • The Slowly Changing Dimension is converted in place. The dimension table keeps its design (surrogate key, start and end dates or current flag), and the flow the SCD wizard built after it converts like any other components, instead of being redesigned around Hop's Dimension lookup/update.
  • Keys match as SQL Server matches them. A Lookup without a full cache runs SSIS's own per-row query, so the database's collation decides; the Slowly Changing Dimension compares business keys without trailing spaces and case, as SQL Server's default collation does.
  • Script Components can be translated, on request, and are always flagged. By default they are placeholders with their C# kept next to the pipeline. With --translate-scripts, a language model translates them during the import; see Translate Script Components.

What it does not do​

  • Passwords: SSIS does not keep passwords in a package in clear text. A connection that logs in with a user name gets a ${SSIS_<CONNECTION>_PASSWORD} variable as its password; Windows authentication is reported per connection. See Finish connections and passwords.
  • Script Tasks: their C# or Visual Basic is written next to the workflow, and the task is a placeholder to port by hand.
  • Less common components and tasks: Fuzzy Grouping, Term Extraction and Term Lookup, Pivot, Data Profiling, XML, Web Service, Analysis Services and CDC tasks, Foreach enumerators other than files, items and ADO rows, OnWarning and OnInformation handlers, and third-party components become placeholders. The component mapping lists what converts.
  • Deployment formats: it reads package folders, not .ispac archives, .dtproj build configurations or SSIS catalog environments. Packages saved by SSIS 2005 or 2008 are reported as failed; upgrade them in SQL Server Data Tools first.
  • Schedules: SQL Server Agent jobs are not in the package files.
  • It does not validate the result. Do that with a parallel run.

The importer's repository is hop-importer-ssis; its compare/ folder holds the SSIS-to-Hop comparison runs described above.

What we tested it on​

Two test corpora of public sample projects: the SSIS projects of 27 GitHub repositories under the MIT or Apache 2.0 licence, listed in ssis-github.tsv (414 packages in 82 project folders), and the sample solutions of Packt's SQL Server 2017 Integration Services Cookbook under the MIT licence (48 packages in 9 project folders, starter and solution versions). Copies in obj/ folders are not counted. They are other people's sample, course and project work, not your project; the migration report of your own import gives your figures.

ResultWhat was measured
Converted automatically85% of components (7,922 of 9,331); 88% fully or partly (8,243)The reports of an import of each corpus; 462 of the 462 packages convert. 9% (803) are left to a person, mostly Script Tasks and Script Components, whose C# no rule-based importer converts; 3% (285) are third-party components the importer does not know
Runs end to end100% of the data flows (183) and 100% of the package workflows (101) that could be set up run to the end; 0 failPublicCorpusExecutionTest rebuilds the tables each data flow uses in H2, in SQL Server mode, from the package's column metadata, and runs the converted pipelines and workflows unchanged. Only 25% of the data flows (183 of 743) could be set up that way: the others have a placeholder on the path (a Script Component, a third-party component), or query tables, files or servers the package does not describe
Same result as the original97% of the tables compared are the same, row for row (73 of 75, in 6 public projects); the other 2 differ for documented reasonsThe compare/ runs: SSIS on Linux and the converted packages on Hop, from the same data, every table compared. The 2: a package in one project races its own stored procedure; in another, a business key that comes twice in one load, where SSIS's result depends on its buffers. A synthetic project adds 10 more identical tables and one difference, a Merge Join after a case-insensitive Sort

Read the rows separately. Running to the end shows a conversion is complete on data of the right shape, not that it computes what SSIS computes; only the comparison shows that, and only for those projects. The comparison uses SSIS on Linux, not SSIS on Windows.

Where to go next​

  • New to it? Get started imports a public SSIS project, walks you through the report and runs one converted package.
  • Want to see one package converted? Before and after.
  • Working on your own project? The how-to guides cover assessing a project, importing into a Hop project, finishing connections, translating Script Components and running in CI.
  • Looking something up? The reference has every option, every component and every report section.