0

I have an excel file which is linked to a SQL Server table using an External data connection. This table is refreshed weekly by an upstream process. For some reason, our users are unable to refresh the data by themselves since they run into authentication issues even though the password is saved and embedded in the file. They want me to email the file each week after the data is refreshed.

Currently, the table is refreshed via an SSIS process. I could use an Email Task and send the file as an attachment.

IS there a way that I can automatically refresh the file without opening or any other manual intervention at the time of execution?

I looked at some of the other solutions that have been posted online but most of them have the source data in a separate tab. Our data is external.

Improve this question
3
  • I remember Excel on Mac could not perform refresh. Please verify if their computers are Mac. You could configure Excel to refresh data from external link on open. Hope it helps. Commented Nov 28, 2018 at 17:35
  • @Lucky Is the file meant to be read-only or are the users going to manipulate it? If the former, then I would recommend placing the file on the network drive and making it read-only (set a password to modify it). Frankly, this requires a lot of work and I don't like Excel's idea of concurrent user access. If the latter, I would recommend just e-mailing the file via a send mail task. At any rate, You should consider rationalizing allowing multiple users ability to hit the database for data. If all users keep hitting it, and there is a lot of data, then that may not be optimal. Commented Nov 29, 2018 at 16:04
  • Thank you @JWeezy for the response. Yes, emailing is not the problem here - automatically refreshing it when the sql server data is updated. It is a pivot table file so users are expected to manipulate it. Commented Nov 29, 2018 at 16:57

1 Answer 1

Reset to default
0

You can set spreadsheets to refresh automatically on a schedule using powerpivot and sharepoint.

https://support.office.com/en-us/article/manage-a-data-refresh-schedule-218448bc-24fa-4729-a28e-3ce5ef003e95

it creates a sql server agent job in the background to do the refresh from what I remember when I set this up at my last job, so you could tie it in to SSIS as a next step after it runs on the agent job. All sharepoint files can be picked up using windows explorer, so you could get it from it's file path there to email it out. Or you could just give them the sharepoint link, but now it will always be up to date.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you @BrandonFrenchak - any idea if this approach works for Office 365 as well? Or is that sharepoint now? I dont believe we use Sharepoint exclusively here.
Doesn't seem to be possible in sharepoint online if the data is in excel. You could recreate the excel file in Power BI, and then Power BI has a feature to refresh data on a schedule. Can then publish the report to the Power BI Portal or to SharePoint Online.
Other option would be to use like cmd or powershell in an SSIS sql task to open and refresh the spreadsheet, or just in another step in an agent job. There's some settings in SQL Server you have to change to allow this. Too long to explain here, but googling should turn up how. Here is someone doing something like that. stackoverflow.com/questions/47159268/… So 3 answers are 1. Sharepoint on prem and powerpivot, 2. Recreate spreadsheet in Power BI instead and refresh in the cloud 3. Script the refresh in cmd or powershell.
I was wrong, you can have excel spreadsheets be refreshed by Power BI as well. learn.microsoft.com/en-us/power-bi/refresh-data
Thank you all for your responses. Powershell with SSIS worked for standalone but did not when executed via SQL Agent. So, I modified it to run as an independent powershell step in SQL Agent which works well.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.