0

Sourcing Reporting Services Output in a Data Model

Does anyone know if it is possible to use an SSRS output (CSV, XML, or Excel) as a source in a Data Model?  We tried to use the URL option without luck but aren't sure if it is a security problem or what.

Here is the basics of the call we are trying to use:

https://<My_Server>/ReportServer/Pages/ReportViewer.aspx?/Prod/COVID19&rs:Command=Render&rs:Format=CSV

When calling that URL, it immediately downloads the file locally.  Can it be harnessed to go straight into a Data Flow?

If this is not possible due to the manner in which SSRS transfers the file, does anyone have an idea of how it might be accomplished using some other method like Python or something else?

3 replies

null
    • Michael_Raam.1
    • 1 yr ago
    • Reported - view

    Hi,

    If  SSRS does not allow to read the data with out downloading the file, you need to check the SSRS documentation, than you can consider the following.

    You can check how SSRS based data can be consumed in an ETL process.

    1. Run a process that downloads the SSRS based data into a file/s on a folder you have access to.

    2. Run a process that reads the data from the file/s in to the model.

      • Jason_Picker.1
      • 1 yr ago
      • Reported - view

      Michael Raam Thanks, Michael.  I can certainly look at building a process around downloading the file and then using standard Data Flow features to pick up the file and importing it into a model.  I was hoping I wasn't the first to try this so I could leverage someone's solution but sounds like I am blazing the trail 😁

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Jason,

    You can configure a SSRS report as an Atom Data Feed.

    https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/generate-data-feeds-from-a-report-report-builder-and-ssrs?view=sql-server-ver16

    Pyramid has an RSS Data Feed source block which can consume RSS or Atom feeds, so once you have the RSS Feed URL, just paste it into the RSS Feed Source block properties and off you go. 

    Hope that helps.

    Ian

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 3Replies
  • 97Views
  • 3 Following