Exporting data from Semantic model

You're in a tricky but not uncommon situation — pulling data from an OData source (Jira) into Power BI works fine with token-based auth, but exporting it out of the semantic model (or transforming it via SQL) is tough in an on-premises-only setup without Fabric or cloud-based tools like Power Automate. Here's the core issue:

• Power BI semantic models are not designed for downstream ETL consumption directly.

• You can't export or connect SSIS to semantic models without going through Power BI Service or Fabric, which your client can't use.

• SSIS doesn’t support blank usernames for token-based OData access natively — that’s the blocker with going direct from Jira to SSIS.

 Practical Options:

1. Use Power BI Desktop as a "bridge":

   • Load Jira OData into Power BI Desktop (as you already do).

   • Export data from the Power BI model to flat files (CSV) using DAX Studio or Tabular Editor.

   • Then, have SSIS pick up those flat files for processing.

   • This can be automated with scripting and scheduled tasks.

2. Use PowerShell to Extract from the Semantic Model:

   • With Power BI Desktop running, connect to the local model via localhost:xxxxx and use PowerShell with TOM (Tabular Object Model) or DAX Studio to extract data.

   • Save to flat files or push to your SQL database from PowerShell.

3. Use a Middleware ETL Tool with OData Support:

   • Tools like KingswaySoft (SSIS add-on) allow advanced authentication scenarios for OData, including custom headers (so you can manually set the token as the Authorization: Basic header).

   • This bypasses the username restriction in native SSIS.

4. Use Custom .NET/PowerShell App to Pull and Push:

   • Build a lightweight custom app or script that pulls data from Jira (via the OData feed with token auth) and pushes it into SQL Server — effectively replacing SSIS for this job.

   • Once in SQL Server, you can continue the rest of your ETL via SSIS.

Not Viable (without Fabric):

• Dataflows Gen2 – require Fabric capacity.

• Power Automate export from semantic model – also needs Fabric.

• Direct SSIS to Power BI semantic model or dataset – not supported.