question

Spunny-0938 avatar image
0 Votes"
Spunny-0938 asked IsabellaZhangMSFT-7746 answered

dynamic excel file as datasource for SSRS

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://docs.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.






sql-server-transact-sqlsql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsabellaZhangMSFT-7746 avatar image
0 Votes"
IsabellaZhangMSFT-7746 answered Spunny-0938 commented

Hi @Spunny-0938
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.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Isabella, my first option was that which we don't want to do because there are 50 reports which need to run daily. So, need to create 50 tables and stored all the data. We just need to transform csv file data into report. Nothing else to do. So, prefer not to stored data in database.

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsabellaZhangMSFT-7746 avatar image
0 Votes"
IsabellaZhangMSFT-7746 answered

Hi @Spunny-0938

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.