I'm trying to use PowerQuery Source in SSIS as mentioned in the thread Is there any way to invoke PowerQuery/M outside of Excel or PowerBI?. I have a working package targeting SQL Server 2017. I can run it from Visual Studio perfectly fine. But if I deploy the package and try to execute it (using the same parameters) from within Management Studio or using C# code, I get the following:
Data Flow Task:Error: The managed pipeline component "Microsoft.PowerQuerySource" could not be loaded. The exception was: Could not load type 'Microsoft.PowerQuerySource' from assembly 'Microsoft.SqlServer.PipelineHost, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'..
My understanding was this PowerQuery Source is in "preview", so that's about the only reason I can think of it not working. Apparently Power Query Source (Preview) used to say this:
Power Query Source can only be used in SQL Server Data Tools (SSDT) and Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF).
Now it says
Power Query Source can only be used on SQL Server 2017/2019 and Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF)
Seems like it should work, as I'm running SQL Server 2017, Developer Edition. Is there maybe something I'm missing when deploying this?
