SSIS - integrate with Power Query (Excel Add-In)
Integrate SSIS with the Power Query Excel Add-In as a Data Source. Solution needs to be all on-prem to avoid typical cloud concerns. Ideally can reach into a workbook for a specific Query or function, pass in function values if required, and trigger a refresh of a Query or all Queries.
Richard Boland commented
But don't loose the beautiful SSIS debug screen! Power Query right now is awful at letting you know what's happening...
Daniel Bartley commented
Yes power query all the things until on-prem is dead.
But cloud first guys.
Adil Majeed commented
Like Database to Azure SQL Datawarehouse, Analysis Services to Azure Anslysis Service, why not SSIS to Azure SSIS or in form of shape, I am supporting.
Jakub Kaczanowski commented
Agree, suggestion is up on connect too - https://connect.microsoft.com/SQLServer/feedback/details/1046883/add-powerquery-source-connector-to-ssis
Mike Honey commented
I should add, with the option to complete the refresh by either updating the Excel file, or by picking up the output from the Query and passing it along an SSIS dataflow - Power Query as an SSIS Data Flow Source.