dynamic excel file as datasource for SSRS

Question

Hi,
We get excel files dynamically every day (like new folder gets created for each day) on to shared drive from customer
For example
2022 folder
04 April folder
1 day 1 folder
xxx_04012022.csv
2
3
4
.
.
20
xxx_04202022.csv
yyy_04202022.csv

We created SSRS report with Parameters (
1. SourcePath parameter - parameter hidden and dynamically getting today's date and building Path like '\shareddrive\yyyy\mm\dd\xxx_+ today() + '.csv'
2. FileName parameter - parameter hidden and dynamically build filename
3. Rundate ( date the user wants the data to see)

These 2 gets send to stored procedure where csv file data gets imported into temporary table and select query to send results to reports like this:
SET @sqlCmd =
'SELECT *
FROM OPENROWSET(BULK ''' + @pFileDirectory + @pFileName + ''',
FIRSTROW = 2,
FORMATFILE = ''' + @pFileFormat + ''') AS T'

--print @sqlCmd
INSERT INTO @LoadT
EXEC sp_executesql @sqlCmd;

IN SSRS I used the stored procedure as data source with above code.
When user runs the SSRS report in report manager, it errors that user doesn't have bulk admin rights and should be added to bulkadmin.

For security reasons, my DBA is not ready to add users or role to bulkadmin.

We have below options:

1. SSIS package to import data from these files and use it as datasource. We do not want to do this because there are 50 reports that just need these files transformed into SSRS. No need to store the file data in database. So, architect is not liking to store tons of data.
2. Do not want to do excel engine like specified here: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15
3. REST API is still do not render or export to PDF

What options do I have to avoid security issues and access file data.

Answer 1

Hi @Spunny
Maybe you can create a daily repeated job to import the data from csv file to a table ,then in report you just need to select * from table ,right?
Just a guess，I don’t know if it works，if it doesn't，please feel free to let me know.

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you convert the file to JSON you may try to use OPENJSON function ?

Answer 3

Hi @Spunny

You can use an ODBC connection to connect to a CSV file

Here is the post I take a reference :ssrs-importing-data-from-a-csv-file-to-use-as-a-data-source-on-a-report-server
And here is a video about how use CSV file as Data Source in SSRS Report:How to use CSV file as Data Source in SSRS Report
Hope this will help you.

Best Regards,
Isabella