Forum Discussion
Advocate IReplacing Magic with MS Fabric for AS400 ODBC extraction and CSV delivery to a Window
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.
Super UserHello 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:
This approach provides a straightforward replacement for Magic ETL with minimal disruption in my view.