Skip to main content

Assess an SSIS project

Before you plan a migration, import all your packages once into a scratch folder and read the report. It costs one command, changes nothing in SSIS, and tells you where the work is, package by package and component by component.

1. Collect the packages​

Use the project folders from source control or from the SQL Server Data Tools solution: for each SSIS project, the folder with its .dtsx packages, its project connection managers (*.conmgr) and Project.params. Put them under one folder, one sub-folder per project; the importer reads every folder that holds packages as one project.

  • Packages must be in the SSIS 2012 or later format. Packages saved by SSIS 2005 or 2008 are reported as failed; upgrade them in SQL Server Data Tools first.
  • The importer reads package files, not .ispac archives or the SSIS catalog: use the project's source files.
  • The obj/ folders a build leaves behind are skipped, so you don't need to clean them out.

2. Import into a scratch folder​

java -jar importer-ssis.jar <packages-folder> /tmp/ssis-assessment

or, with the plugin installed, <hop>/hop-import.sh -t ssis -i <packages-folder> -o /tmp/ssis-assessment (with full paths). With the default --skip-existing, an import leaves files that already exist in the output folder alone. Use an empty folder for an assessment.

3. Read the report​

Open /tmp/ssis-assessment/ssis-migration-report.md, in this order:

  1. Totals: the size of the project, and how much of it was converted with no placeholder (AUTO). The report calls a package a "job".
  2. Most frequent source components: what the project is made of. Many Microsoft.ScriptTask or Microsoft.ScriptComponentHost entries mean hand work; many Microsoft.ExecuteSQLTask entries mean SQL parameters to check.
  3. Needs review: every flagged component with the reason. Group it by reason: the same reason on forty packages is one decision, made once.
  4. Per job: packages with nothing flagged can move first; they are also the quickest way to try your validation process.
  5. Database connections: how many connections there are, which use Windows authentication, and which need a password variable.
  6. Packages that failed to import, at the end, if there are any.

4. Size the work​

For each reason in Needs review, decide who does it and how long one takes, then multiply. Add the work the report does not see: SQL Server Agent schedules, SSIS catalog environments, stored procedures and tables the packages expect, files and programs they use at run time, and validation.

Keep the scratch output: the converted packages with nothing flagged are a good first batch to validate.