SSIS - SSRS Rendering Clash Between EXCEL and EXCELOPENXML

1
votes

I am attempting to render an SSRS Dashboard (ssrs 2016) to Excel (2010).

My Dashboard has 4 parameters as

StartDate, EndDate, Company, NumberOfJobs.

StartDate, Company and NumberOfJobs will all render perfectly when using render to xlsx (EXCELOPENXML). They will not render when using EXCEL as the format in my SSIS VB script.

EndDate will not render using EXCELOPENXML but is fine when using render to xls (Excel).

If typed into IE, the URL of the report will happily render all 4 to xlsx, giving the option to open Excel or Save to File. However, when it is run using SSIS, it generates a 0kb file and gives the error message

excel cannot open the file because the extension is not valid.

I have noticed that EndDate also breaks rendering to PDF.

Any ideas how I could merge the rendering when running a VB script?

Many thanks, Stephen

1
excelvb.netreporting-servicesssisssrs-2016
I'm not clear what it means when you say that a parameter won't render. Does your dashboard have 4 different reports? Do you have any screenshots to demonstrate what you are seeing? – Wesley Marshall
Hello Wesley, I have found that if I remove the section &rs:Command=render the report will actually run. This is just one report with 4 input parameters to filter on. However, it completely ignores the EndDate parameter (which was the parameter which was causing the rendering issue). You can run the report from SSRS perfectly, however, the results when using SSIS and a fixed URL either don't work or create incorrect results. Here is an example of the URL. url = "svr-xxx/ReportServer?/Dasboard&StartDate=1/1/…" – Stevie Gray
I see several things potentially wrong with the URL you provided. 1. No protocol in front. 2. Dashboard is misspelled (does this match your report name?) 3. IIRC, dates should be passed as MM/DD/YYYY, which your enddate is not. 4. You end with a double quote (%22) but I didn't see a begin quote. – Wesley Marshall
Thanks Wesley, I had been changing the code to hide any sensitive information. Basically, the problem I have is that if I hard code all the parameters into the URL, I can ctrl+click the address and it opens up a perfect Excel document. However, when I try to use the same URL in SSIS, the Excel document does not create correctly. The problem is down to the endDate, which will ruin the Excel document. As stated in the question, endDate seems to work ok if the file is saved as xls and rs:Format=Excel. However, the other 3 parameters require the URL to have xlsx & rs:Format=EXCELOPENXML – Stevie Gray
Did you try reformatting the end date you are passing from DD/MM/YYYY to MM/DD/YYYY, per suggestion 3 from my previous comment? – Wesley Marshall

1 Answers

0
votes

Many thanks to Wesley for his help. Dates should be passed as MM/DD/YYYY. Being from the UK, I was using the format DD/MM/YYYY.