SCCM 1606 + SQL 2016: Report subscription failed

Today my lovely handsome colleague Antti told me that SCCM report subscription doesn’t work, when tried to create report subscription send email attach with excel.

Errors

• Web.Services.Protocols.SoapException: A delivery error has occurred. —> Microsoft.ReportingServices.Diagnostics.Utilities.DeliveryErrorException: A delivery error has occurred. —> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidExtensionParameter: One of the extension parameters is not valid for the following reason: The value ‘EXCEL’ is not valid for setting ‘Render Format’. at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, Boolean isDataDriven, DataRetrievalPlan DataRetrievalPlan, String Description, String EventType, String MatchData, ParameterValueOrFieldReference[] Parameters, Guid batchId, String& SubscriptionID)   at Microsoft.ReportingServices.WebServer.ReportingService2005.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, String Description, String EventType, String MatchData, ParameterValue[] Parameters, String& SubscriptionID)

Problem was we are using SQL 2016 and SCCM 1606.

Same issue has mention in here, and there is already work arround, use web report portal. https://www.windows-noob.com/forums/topic/14479-the-create-subscription-wizard-completed-with-errors/

But I want to use SCCM admin console, and I don’t want to recreate all my report subscriptions again. 🙂

I found a Microsoft Article said that “The Microsoft Excel 2003 rendering extension is deprecated.” “The EXCELOPENXML extension defines the Excel renderer for current (.xlsx) Excel files. The EXCEL extension defines the Excel 2003 version. Visible = “false” indicates the Excel 2003 renderer is hidden”

https://msdn.microsoft.com/en-us/library/dd255234.aspx?

So seems problem is SCCM 1606 report subscription is trying to use EXCEL render instead of EXCELOPENXML render. Maybe?

Well, I am not an expert of SQL, so I am not going do the guessing game. But I do manage to “fix” it, or AS-IS work arround.

Modify your rsreportserver.config file. Default path will be in  “C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config”

Remember: Backup your rsreportserver.config file

Change these :

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>

To these:

<!--Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/-->
<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<!--Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/-->
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>

I put Visible=”false” in EXCELOPENXML, because if I don’t put anything, you will see two “Excel” when try to export reports from web portal. If I put Visible=”false” in EXCEL, then subscription will failed again.

Same issue with Word, so I modified same settings for word.

Close SCCM Admin console and open it again, then you can create a new report subscription (send email and attach Excel) to test it.

You can manually kick off your report subscription jobs:

1. Open SQL Server Management Studio
2. Connect to the Report Server.
3. Choose ReportServer for the database.
4. Execute the following Query to determine the SQL Server Agent Job Name (Job ID).

/*Execute SSRS Subscription Manually*/
/*Connect to Database ReportServer*/
SELECT
     S.ScheduleID AS SQLAgent_Job_Name
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)

5. Open SQL Server Agent, and run the job.

I hope will have offical solution soon, and I will keep this post updated.