Greetings,
After spending an inordinate amount of time triaging errors when migrating reports from PBI RS to the Service, I thought I would save folks some time and share my experience in resolving the "invalid parameter" error message that kept creeping up after report migration and setting up the data source connection to the Gateway.
Process
1) Publish report to Power BI via the Report Manager in PBI RS - no errors
2) Connected to Data Source via Enterprise Gateway
3) Ran the report in which the parameters appeared to be populated correctly but then failed
4) Error message indicating "invalid parameter" (more below but not exhaustive)
We used to have a standard of entering developer comments (using -- before each line) in the Oracle SQL used in the Datasets of the Paginated reports. Colons were also used in the comments to delineate values after the "field names" as we could parse the strings for reference/inventory management. This worked well for the reports published to PBI RS (on-prem) but once published to the Service, the interpreter ignored the dashes of the comments which caused the colons to be evaluated as an Oracle parameter value.
The solution was to remove the comments as they are no longer required which resolved the in the Service.
Environments:
* SQL Server 2016
* PBI RS Version1.19.8779.19175(January 2024)
Data Warehouse (Dataset Query DB)
* Oracle 19c
Paginated Reports:
* Shared Report Parts
* Shared Data Source
* SQL and Query Parameters used in Datasets
Portion of Error Message:
Unable to render paginated report
A data source used by this report returned an error. If you continue to experience this error contact the report author or data source owner quoting: DM_GWPipeline_Gateway_ProviderDataAccessArgumentError. Received error payload from gateway service with ID 139675: Async operation fedca7d6-a82d-45f2-8cfb-deb0b37f96db is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.DataAccessUserErrorDueToArgumentException: Failed to access target data source because at least one of the passed arguments is invalid. Please check the data source connection string.
GatewayPipelineErrorCode=DM_GWPipeline_Gateway_ProviderDataAccessArgumentError
GatewayVersion=3000.226.5 --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: ArgumentException:<ccon>Invalid parameter binding
Parameter name: adatar</ccon>
GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=ArgumentException
InnerMessage=<ccon>Invalid parameter binding
Parameter name: adatar</ccon>
InnerToString=<ccon>System.ArgumentException: Invalid parameter binding
Parameter name: adatar
I hope this information saves others time as they migrate paginated reports from on-prem to the cloud.
-Liam
