Use SSRS exported Excel as SSIS Excel Destination?

0
votes

I have an SSRS report that exports to Excel in xlsx format. I'm trying to then use SSIS to export a large dataset as a new tab to that report, but when I try to use the SSRS exported xlsx file as an Excel Destination in SSIS I get a bunch of errors and it won't recognize the source.

But, if i open the xlsx file exported from SSRS and manually save it again as xlsx, then it will work.

Any idea why this is happening? Is it not possible to use an SSRS exported xlsx file as an Excel Destination in SSIS?

Thanks.

1
sql-serverexcelreporting-servicesssis
Why are you trying to add data to the Excel doc after is has been exported? Is there a reason you can't just add the large dataset as a another tab in the report itself? – Nathan Griffiths
The main reason is that the dataset has 100,000 rows and SSRS chokes on trying to export that many rows to Excel. SSRS is creating a summary but I also need to include the detail. The detail is just very large. – WilliamB2
Perhaps you could instead have a link on a second worksheet in the report which generates a separate report of the dataset to CSV (i.e. use URL access to specify the export format in the link) – Nathan Griffiths

1 Answers

0
votes

I faced the same issue after using a SSRS-exported excel file as Excel Source in SSIS. It seems SSRS encode the file and include several SSRS specific characters that Excel doesn't interpret and aren't visible from Excel.

You can export the report as a plain text file then use a Flat file connection to flow the data.

Let me know if this helps you.