SSIS - Power Query Source - How to use a variable for the path? Getting Errors.

Question

I need to be able to pass in a variable into the Power Query Source module for the file path to an .xlsx file. This will make it easier to deploy the SSIS package on test and dev servers where the path will be different.

If I hard code the file path in both the M Query Text and the Connection Manager, the Power Query module runs fine.

The first part of my solution is to use the "Single Query from Variable" Query mode in the Power Query Source module and update the path inside the variable holding the query. This actually works fine. I can update the file path within the query variable and the Power Query Source module passes.

The second part of the solution does not work as expected. I pass a variable that holds the path to my data file (It looks something like "C:\datalocation\datafile.xlsx") by assigning it to the DataSourcePath in Expressions for the Connection Manager. It errors EVERY TIME with:

Error: 0xC004701A at Process ProcessData, SSIS.Pipeline: Power Query Source failed the pre-execute phase and returned error code 0xC0047072.

At this point, the Power Query Source module will continue to fail even if I change it back to hard coded query and paths. After looking around, I've noticed that under "columns" within the module all of the External Column locations have been cleared out.

Update: I'm really not understanding what is causing the error. I managed to get it to work in a separate "test" package, but I'm not sure why and cannot duplicate the results. Below is the full error I'm seeing. What is this telling me? Is there a way for me to get more information. I've placed a breakpoint on the preexecute event and my variables for the path and the query look good.

Error: 0xC0047062 at Process ProcessData, Power Query Source [35]: System.Runtime.InteropServices.COMException (0xC0047072): Exception from HRESULT: 0xC0047072
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSExternalMetadataColumnCollection100.FindObjectByID(Int32 lID)
at Microsoft.SqlServer.IntegrationServices.PowerQuery.Framework.ExternalMetadataComponent.GetExternalColumnMapping(IDTSExternalMetadataColumnCollection100 externalColumns, Int32 externalColumnLineageId, Int32 hBufferType, Int32 lineageId, DataType dataType, String columnName)
at Microsoft.SqlServer.IntegrationServices.PowerQuery.Framework.ExternalMetadataComponent.GetExtendedBufferMappings(IDTSOutput100 output)
at Microsoft.SqlServer.IntegrationServices.PowerQuery.PowerQuerySource.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)
Error: 0xC004701A at Process ProcessData, SSIS.Pipeline: Power Query Source failed the pre-execute phase and returned error code 0xC0047072.

Any idea what is going on? I have gone round and round with this. The only way to get this module to work again is to completely remove and recreate it.

Thanks,
William

Answer 1

Power query is not currently supported here on Q&A. The product group for Power Query actively monitors questions over at
https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

--please don't forget to Accept as answer if the reply is helpful--

Answer 2

@Anonymous

Is SQL Server Integration Services supported? This is in regards to passing user variables in SSIS to the Power Query Source module within SSIS and the corresponding Connection Manager.

My M Query code works fine, so it's not Power Query that I'm having a problem with.