question

SQLDHB avatar image
SQLDHB asked

Import Excel file through SSRS Dataset

My objective: Execute a stored procedure from an SSRS report that will import data from an Excel file in a network folder, transform the data, and output to a SQL table. The output table will then be linked to an SSRS dataset. **Note: I do NOT want to link the Excel file directly to the SSRS report as a data source.** Attempts so far: I've created a test sproc "udp_ImportTest" that simply imports the spreadsheet and writes the results to the output table with no transformation. This sproc is executed by a SSRS dataset. I tried 2 different approaches and both work perfectly when executed from SSMS but both fail when invoked from SSRS. **Approach 1**: Import the spreadsheet using OPENROWSET... CREATE PROCEDURE dbo.udp_ImportTest AS TRUNCATE TABLE temp_AUMExtract; INSERT INTO temp_AUMExtract SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 8.0; Database=\\SCDATA\Import\AUM_Extract.xls' ,'SELECT * FROM [Sheet1$]') SELECT * FROM temp_AUMExtract GO Result: Output table filled and displayed successfully However, when I tried to create a Dataset in SSRS... ![alt text][1] It throws this error: ![alt text][2] **Approach 2**: So I tried creating a linked server rather than use OPENROWSET... 1. *Create Linked server* EXEC sp_addLinkedServer @server= N'AxysAUMExtract1' ,@srvproduct = N'Excel' ,@provider = N'Microsoft.ACE.OLEDB.12.0' ,@datasrc = N'\\SCDATA\SCMAdmin\Import\AUM\AUM_Extract.xls' ,@provstr = N'Excel 8.0; IMEX=1; HDR=No' ; SELECT * FROM AxysAUMExtract1...[Sheet1$] Result: Output from linked server successful. 2. *Change OPENROWSET to Linked Server in the udp_ImportTest stored procedure:* TRUNCATE TABLE temp_AUMExtract; INSERT INTO temp_AUMExtract SELECT * FROM AxysAUMExtract1...[Sheet1$] SELECT * FROM temp_AUMExtract ; Result: output successful But when I tried again to create SSRS dataset that calls udp_ImportTest I got the same error (7399) as above. I have followed all of these steps that I picked up from previous posts: 1. Download and install “AccessDatabaseEngine_x64.exe” on the server. 2. Enable the "show advanced options" and “Ad Hoc Distributed Queries” configuration settings 3. Register the Microsoft.ACE.OLEDB.12.0 provider for AllowInProcess & DynamicParameters properties 4. Add a linked server login for the SQL Authenticated account 5. Give access to Read, write and execute permission to Temp directory to the Service account of SQL Server. Is there a way to make this work? [1]: /storage/temp/4439-cimagesdatasetproperties.jpg [2]: /storage/temp/4440-cimagesssrs-linkedserver-error.jpg
linked-serverssrs-2012openrowsetdataset
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLDHB Have you tried using the normal Excel spreadsheet in place of the older Excel 1997 - 2003 file that you use? If you can convert the file to the proper Excel format and change the Excel 8.0 to Excel 12.0 or Excel 14.0 in the openrowset specs as needed then you can check whether the old format is the source of the problem. Also, does the query work if executed in SSMS? If yes then the problem is probably related to permissions. Whatever account that executes openrowset must have permissions ***to*** the TEMP folder ***of*** the SQL Server Service account. Just to check the permissions, find the TEMP folder of that service account (**c:\\users\\ServiceAcccountName\\AppData\\Local\\Temp**) and give Everyone full control (you can reduce the permissions later once the problem is resolved).
0 Likes 0 ·

1 Answer

·
SQLDHB avatar image
SQLDHB answered
Hi Oleg. As I mentioned above, the OPENROWSET works fine when called from SSMS. Excel 8.0 reads xls files. I change it to 12.0 when I read xlsx files. And yes, I granted full control access rights to the Temp folder to the SSRS service account. What I have since discovered is the import succeeded when I changed the SSRS report data source credentials from Windows Authentication to my SQL Server login. Upon further research, I think I need to register an SPN for the SSRS service account in order for Windows Auth to work. I tried executing a SetSPN from the server's command line... Setspn -s http/ SCMSQL.companyname.com:80 MSSQLSSRS ...but was blocked by "insufficient access rights". Still trying to figure out how to get around that.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLDHB To set SPN (in order to avoid the double-hop issue which causes the credentials of the SSRS account not to be forwarded) one ought to have the domain admin privileges. The solution you found is a good one. I believe what happens is the following: when the SQL Authentication is used, there is nothing to forward, so the SQL Server service account itself is used. In this case there are no problems with permissions. It is possible that without SPN the openrowset call resolves to NT authority anonymous account and because it does not have the permissions to the temp folder of the SQL Server Service account, the call fails. I used to work at the place where we had more than one space vendor so we ended up with data center hosted by RackSpace, some other vendor, and some servers in the head office. With this configuration I could never succeed executing any openrowset statements with SSPI, even the ones to execute a T-SQL statement on the different instance and bring the results back to the calling instance. The only solution was to use the SQL authentication.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.