We get excel files dynamically every day (like new folder gets created for each day) on to shared drive from customer
04 April folder
1 day 1 folder
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 =
FROM OPENROWSET(BULK ''' + @pFileDirectory + @pFileName + ''',
FIRSTROW = 2,
FORMATFILE = ''' + @pFileFormat + ''') AS T'
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.