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.

Updated ADF Pipeline Activity for SSIS packages

Case
In April 2018 Microsoft added the SSIS pipeline activity to ADF. A couple of days ago the released a new version. What are the changes?

ADF - Execute SSIS Package activity

Solution
The big difference is that you can now select your packages and environment instead of typing the full paths manually, but there is also an new "Manual entries" option which allows you to set parameters (and connection managers) directly within the pipeline.

Selecting package and environment

See below the old version of the SSIS activity where you had to type the full paths manually.

The old manual entry

Conclusion
Again much easier then before, but in case of package errors still not very helpful. It still forces you to search for errors in the SSIS catalog. If you are using the ADF monitor to the check for errors I would probably still prefer the Stores Procedure activity.