Replacing Magic with MS Fabric for AS400 ODBC extraction and CSV delivery to a Window

[mustaphaben]

Hello Fabric Community,

We are currently analyzing a migration scenario where the goal is to replace a Magic ETL/ESB solution with Microsoft Fabric.

Current process:

AS400 database
   ↓
Magic ETL/ESB
   ↓
CSV files generated in a Windows shared folder
   ↓
SSIS package
   ↓
SQL Server

Target idea:

AS400 database
   ↓ ODBC connection
Microsoft Fabric Data Pipeline
   ↓
CSV files generated in a Windows shared folder
   ↓
Existing SSIS package
   ↓
SQL Server

The objective is not to migrate the entire SQL Server / SSIS stack to Fabric immediately.
As a first step, we only want to replace Magic for the AS400 extraction and CSV file delivery part.

From my understanding, this solution could potentially be implemented using:

• the ODBC connector to access AS400;

• the On-premises Data Gateway;

• a Copy Activity in a Fabric pipeline;

• the Folder connector to write CSV files into a Windows shared folder.

I have a few questions:

1. Is this architecture supported and recommended with Microsoft Fabric?

2. Can a Fabric pipeline read from an on-premises AS400 source via ODBC and write to a Windows shared folder?

3. Would you recommend this approach, or would it be better to use SSIS directly to extract data from AS400 and generate the CSV files?

4. Is there a better transitional architecture before a full migration to Fabric Lakehouse / Warehouse?

Thanks in advance for your feedback, known limitations, or best practices.

[deborshi_nag]

Hello @mustaphaben 

Microsoft Fabric Data Factory can indeed connect to on-premise data sources, and Microsoft documentation does confirm support for IBM Db2 and other connectors. However, for your specific need - i.e. replacing Magic ETL while keeping Windows shared folders and SSIS unchanged - I recommend Azure Data Factory, because it is a safer and more suitable option for the following reasons:

- It supports IBM Db2 through a self-hosted integration runtime

Copy data from DB2 - Azure Data Factory & Azure Synapse | Microsoft Learn

- It provides a network file share connector

Copy data from/to a file system - Azure Data Factory & Azure Synapse | Microsoft Learn

- It supports delimited file formats like CSV

Delimited text format in Azure Data Factory - Azure Data Factory & Azure Synapse | Microsoft Learn

- The self-hosted integration runtime in a Copy Activity can access both the source and sink, which fits your setup since both AS400 and the Windows share are on the same network.

The architecture would be:

AS400 / IBM Db2 (on-prem)

   ↓

Azure Data Factory pipeline

   ↓

Self-Hosted Integration Runtime (on-prem Windows VM/server)

   ↓

CSV files written to existing Windows shared folder

   ↓

Existing SSIS package

   ↓

Existing SQL Server

This approach provides a straightforward replacement for Magic ETL with minimal disruption in my view.

[tayloramy · Accepted solution]

Hi @mustaphaben, 

I would avoid this approach, while Fabric *can* read from an on prem database and write to an on prem file share, that is not what it is designed to do. It is designed to target the cloud. 

If your end goal is to replace the entire process, I would start there. In Fabric it is trivial to ingest data into a lakehouse which you can then access from a SQL endpoint in place of your existing SQL Server. 

In my experience, trying to use Fabric as an on prem to on prem ETL tool functions, but it is quiet inefficient. 

To answer your other questions, can Fabric read from ODBC connections? Yes, as long as the DSN is created on the gateway machine.