Skip to main content

Get started with the 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.

In this tutorial you install the importer in Apache Hop, import a public SSIS project of nine packages, read the migration report, and run one of the converted packages against a SQL Server in Docker. It takes about half an hour, most of it downloading SQL Server.

The project is the MSBI training labs by Amit Naik, published on GitHub under the MIT licence: a project in the project deployment model, with a project connection manager, a master package that runs five child packages, flat files loaded into dimensions, and two Slowly Changing Dimensions. Nothing you need comes from Microsoft apart from the SQL Server container image, and no step needs Windows or SSIS.

Before you start​

  • Apache Hop 2.19.0 or later. See Install Apache Hop.
  • Java 21, which Hop 2.19 needs anyway.
  • git.
  • Docker, for the SQL Server the converted package writes to in step 5. SQL Server images are x64 only; on Apple Silicon, Docker Desktop runs them through Rosetta.

In the commands below, <hop> is the folder Hop is installed in. Run them all from the same folder: Hop's scripts need full paths, so the commands use $PWD.

1. Install the plugin​

Download importer-ssis-<version>.zip from the importer's releases, once published, and unzip it into the Hop folder. The zip holds a plugins/ folder, so it lands in the right place:

unzip importer-ssis-<version>.zip -d <hop>

Start Hop GUI (or restart it). The File menu now has Import from SSIS….

2. Get the SSIS project​

git clone https://github.com/Amitpnk/MSBI-Project.git msbi

The packages are in msbi/SSIS_Project/, next to the project connection manager Global_ConnectionString.conmgr and Project.params; the sample data is in msbi/DataFile/.

3. Import it​

<hop>/hop-import.sh -t ssis -i "$PWD/msbi/SSIS_Project" -o "$PWD/msbi-to-hop"

You can do the same from File → Import from SSIS… in Hop GUI: the dialog has the same options.

Expected result: the log says

Found 9 SSIS package(s) in 1 project folder(s) under …/msbi/SSIS_Project
SSIS import: 9 package(s) converted, 0 failed, 22 file(s) written.

and msbi-to-hop/ holds:

  • nine workflows, one per package: Country.hwf, FactCustomer.hwf, Lab1.hwf, Lab2.hwf, Lab3.hwf, Main.hwf, Product.hwf, SalesPerson.hwf, States.hwf;
  • a folder per package with its pipelines, one per Data Flow Task (Lab1/Load CSV.hpl, …), and in Lab3/ the workflow and pipelines of its Foreach Loop;
  • metadata/rdbms/: three connections, one per database connection manager, among them Global_ConnectionString.json for the project connection manager;
  • metadata/pipeline-run-configuration/ and metadata/workflow-run-configuration/: the local run configurations;
  • ssis-migration-report.md.

There is no environments/ folder: this project's Project.params has no parameters.

4. Read the report​

Open msbi-to-hop/ssis-migration-report.md. With importer 0.1.0, its Totals are:

MetricCount
Jobs9
Components60
AUTO58
PARTIAL2
MANUAL0
UNSUPPORTED0
Flagged for review2

A "job" in the report is a package; a component is a task, a container or a data flow component. The other sections tell you where the work is:

  • Per job: components and flagged components for each package. Seven of the nine have 0 in the last column.
  • Most frequent source components: what the project is made of. Here, flat file sources, Derived Columns, ADO.NET destinations and Execute Package Tasks.
  • Needs review: the two flagged components. In Lab2 and Lab3, a Data Conversion sends its failing rows to an error output that writes a file; the converted Data Conversion has no error handling, so those rows are not routed there.
  • Database connections: the three connections, and what to do on each. All three use Windows authentication, and two are renamed, because AMIT-PC\SQLEXPRESS.CustomerDatawareHouse is not a valid file name.
  • Hop settings: HOP_EMPTY_STRING_DIFFERS_FROM_NULL=Y, which every SSIS migration needs, and how the Slowly Changing Dimensions in Country and Product match keys.

These figures describe this project, which is made of training labs with no Script Tasks or Script Components. Your own project will have its own; see why there is no conversion rate.

5. Run one package​

Lab1 reads a CSV file of customers, upper-cases their names in a Derived Column, and writes them to the table tblCustomer in the CustomerDatawareHouse database. See it next to the original in Before and after.

  1. Start a SQL Server and create the database and the table. The repository ships no SQL; the table has the columns the package writes:

    docker run -d --name msbi-sql -p 1433:1433 --platform linux/amd64 \
      -e ACCEPT_EULA=Y -e MSSQL_PID=Developer -e 'MSSQL_SA_PASSWORD=Hop!Tutorial1' \
      mcr.microsoft.com/mssql/server:2022-latest

    Wait until docker logs msbi-sql says SQL Server is now ready for client connections, then:

    docker exec msbi-sql /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'Hop!Tutorial1' \
      -Q "CREATE DATABASE CustomerDatawareHouse;"
    docker exec msbi-sql /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'Hop!Tutorial1' \
      -d CustomerDatawareHouse -Q "CREATE TABLE dbo.tblCustomer (CustomerCode nvarchar(50) NULL,
      CustomerName nvarchar(50) NULL, CustomerAmount decimal(18,2) NULL, SalesDate datetime NULL);"

  2. Make the output folder a Hop project, so Hop finds the connections in its metadata/ folder:

    <hop>/hop-conf.sh -pc -p msbi -ph "$PWD/msbi-to-hop"

  3. In Hop GUI, select the project msbi. In the Metadata perspective, open the relational database connection AMIT-PC_SQLEXPRESS.CustomerDatawareHouse, set Server host name to localhost, Username to sa and Password to Hop!Tutorial1, and click Test. The package was built for Windows authentication on the author's own machine; this replaces it with a SQL login on yours.

  4. Open Lab1/Load CSV.hpl. In Read from CSV (Flat File Source), set the file name to the full path of msbi/DataFile/Lab1.txt: the package reads D:\Questpond\MSBI\Practice\MSBI\DataFile\Lab1.txt, and the importer keeps paths as they are. Save the pipeline.

  5. Run the package's workflow, with the setting the report asks for:

    <hop>/hop-run.sh -j msbi -f "$PWD/msbi-to-hop/Lab1.hwf" -r local \
      -s HOP_EMPTY_STRING_DIFFERS_FROM_NULL=Y

Expected result: the workflow finishes without errors, and the log shows the ten rows of the file going through:

Read from CSV (Flat File Source).0 - Finished processing (I=11, O=0, R=0, W=10, U=0, E=0)
Customer data ware house (ADO NET Destination).0 - Finished processing (I=0, O=10, R=10, W=10, U=0, E=0)

Check the table:

docker exec msbi-sql /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P 'Hop!Tutorial1' \
  -d CustomerDatawareHouse -Q "SELECT CustomerCode, CustomerName, CustomerAmount FROM dbo.tblCustomer"

It holds ten customers, 1001 to 1010, with their names in capitals (AMIT, SHWETHA, …) and their amounts (100.23, 300.00, …).

When you are done, docker rm -f msbi-sql removes the SQL Server.

Next​