Exporting data from Semantic model

[SalvaC]

Hi all

For one of my clients, I use Power BI to extract data from Jira using an Access Token for Authentication (Basic Authentication, where I enter the Token as the password and leave the username empty).

Jira has the Power BI connector installed, and I retrieve the data directly from there, rather than exporting the raw data from the Jira database.

This is an OData source in Power BI.

This works without issues.

However, I now need to perform some complex transformations, which I would prefer to do in a SQL database, rather than using Power Query. It would be much easier in SQL than in Power Query.

The client has an on-premises-only infrastructure and has no plans to migrate it to the cloud.

Therefore, no Fabric.

The client uses SSIS for the ETL process.

Now I struggle to extract the data from the OData source with SSIS, as I cannot omit the Username when trying to connect to it.

My current idea is to load the data from Jira into a Semantic model and extract it into a format that I can read with SSIS.

I tried Dataflow Gen2, but I need a Fabric Capacity for it.

I looked into Power Automate, and I also need a Fabric capacity to export data from the semantic model.

Does anyone have an idea on how I can do this?

Any idea on how to get the data into the database is greatly appreciated.

Regards

Salvatore

[rohit1991 · Accepted solution]

Hi @SalvaC ,

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.

[SalvaC]

Hi all,

Thank you very much for all the suggestions.

You are awesome.

We have looked into it and will attempt to retrieve the data directly from the Jira database, which is stored on a SQL Server Instance.

It's not my preferred solution, as it requires testing whenever the software is updated.

Even some add-ons will potentially change the database.

But the restriction on place leaves us with little choice.

Thank you again.

Kind regards

Salvatore

[ibarrau]

Hi. If Fabric is not an option, then I wouldn't try to mix that betweenpower bi and SQL. I think the best approach would be trying it over python or even do the transformations in a second power query (Dataflow gen1 for extraction and powerbi dataset getting from dataflow for transformation).

Python would let you run anywhere. You could do it locally or cloud. It can connect with Jira API and do whatever you want. You can run it locally to create files for power bi or even for inserting the tables in a database. You can even run it inside Power Bi (then you need a personal gateway to make it work, but it's an option).

You need to decide. You can use articles like this one for transformations: https://blog.ladataweb.com.ar/post/749212921608978432/python-pyspark-to-m-sql-or-pandas

I hope that helps,

[v-dineshya]

Hi @SalvaC ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below workarounds.

1. You can export data from Power BI Desktop to SQL Server using R scripts embedded in Power BI. Use RODBC or RSQLServer libraries to write data from Power BI tables to SQL Server. Trigger the R script from Power BI Desktop or as part of a scheduled SSIS job.

2. If you are using SQL Server 2022 or later version, SSIS supports Power Query as a native source, You can replicate your Power BI query logic in Power Query Source within SSIS. This allows you to connect to the same OData feed and apply transformations before loading into SQL Server.

3. If your semantic model is deployed to Power BI Report Server or Premium workspace, You can connect to it using SSAS-compatible tools like SSMS or PowerShell. Then use SSIS to extract data from the SSAS Tabular model into SQL Server.

Please refer Microsoft document and community thread.

Power Query Source - SQL Server Integration Services (SSIS) | Microsoft Learn

Solved: Exporting Data from Power BI Report to SQL Server ... - Microsoft Fabric Community

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.

Regards,

Dinesh

[SalvaC]

Hi Dinesh

The PQ Source in SSIS sounds interesting.

If I understand it correctly, I must install the PQ source, as it is in Preview.
Unfortunately I work in a Citrix Session and the rules to install new software is very strict.

I have to check what is possible.

This can take a while.

Regards

Salvatore

[v-dineshya]

Hi @SalvaC ,

Thank you for your update , As you mentioned in your previous response, you need  some time to check the "PQ Source" installation possibilities . Once you have done with "PQ Source" 

Please do let us know.

Regards,

Dinesh