Power Query Source (Preview)

Case
Yes new SSIS functionality! In a previous post I had almost written it off, but Microsoft just introduced the SSIS source that we where all waiting for: The PowerQuery source. How does it work?

SSIS - Power Query Source

Solution
First you need to download and install the latest SSDT (Version 15.9.0) and since it is still in preview, you can only use it within SSDT or on a Azure Integration Runtime in Azure Data Factory.

If you drag the new Power Query Source to your Data Flow canvas and edit it, you can paste your Power Query script from Power BI (or Excel) in the query textbox. So there is not yet(?) an editor, but this should simplify a lot of tasks that could were previously could only be solved with .Net scripting.

Paste your Power Query script in the editor

After pasting the Power Query script go to the Connection Managers pane and create a Power Query Connection Manager by first clicking on Detect Data Source and then by adding the new Connection Manager via the drop down list.

Power Query Connection Manager

Preview notes:

• Only working in SSDT or on a Azure-SSIS IR in ADF
• Web source does not work on an Azure-SSIS IR with custom setup
• Oracle source only works via Oracle ODBC driver on an Azure-SSIS IR

For more details read Power Query Source (Preview)

Conclusion
This new source was announced (and canceled) a long time ago, but it is finally available. This preview version is still very basic and still has some limitations. I'm not sure whether they will integrate an editor like in Excel and Power BI, but  lets hope they will.