Yet more evidence that Power Query is taking over the world: there’s a new Power Query data source for SQL Server Integration Services in the latest version of SSDT. Here’s the blog post announcing it (the actual announcement is halfway through):
And here’s the documentation:
It’s a preview and as such, limited in scope. The main restriction, as the blog post says, is:
At present, to facilitate quick/frequent feedback-gathering and improvement cycles, it can only be used with SQL Server Data Tools (SSDT) and SSIS IR.
There’s also no user interface for generating queries: right now you can only paste in an M query or use an M query stored in an SSIS variable. I don’t think that’s much of a problem though – if you don’t want to write your own M code, you can always generate a query in Excel Power Query or Power BI Desktop and cut and paste from there. You don’t even have to open the Advanced Editor to do this, as I show here.
Beyond the normal excitement about cool new stuff, what are the benefits of having Power Query integrated into SSIS? I’m not an SSIS person so I’m probably not the right person to comment on the benefits for SSIS developers, but I would imagine that it will make it possible to connect to a wider range of data sources and also make it easier to work with certain others, such as Excel workbooks and web services. I’d be interested in hearing your thoughts on this, so please leave a comment.
From the point of view of the Excel, Power BI or SSAS developer, though, it’s clear this will make it much easier and quicker to take user-developed Power BI and Excel Power Query solutions and turn them into a managed, IT-department owned solutions – and this will be incredibly useful.
This is awesome ! Such a great news. As you said: Power Query is taking over the world !
However, I think Power Query users deserve a dedicated channel (from Microsoft) for Power Query news such as this one.
There are hidden gems, functions & features (Intellisense in the formula bar to name one…) which are never shown in the Desktop release videos / blog posts.
There was this idea of powerquery.community.com (mentioned in some public release notes) but I don’t know when it will be released!
I’ve always wanted a way to use Power Query to pull data into SQL Server, say from an ugly xml file. This seems like the best way to do that so far.
Best thing to happen to SSIS since SQL Server 2012.
Having used SSIS in years past, there was often a big challenge to get data from a wide variety of sources and to add to that data shaping could too be difficult in SSIS whilst in Power Query its a simple click. I feel this will go a long way to allow a lot more data integration into data warehouses
Thanks for the update! I missed this update, including the better integration of SSIS in Azure Data Factory.
I simply don’t understand this new feature for SSIS. Development for SSIS is already dead for years and then suddenly *bam* major new feature. Would it be that the ADF team is experimenting with Power Query to build up experience? I’d rather have Power Query in Azure Data Factory…
Now we start the new proces: which combination of SSDT is compatible with which version of Visual Studio 2017. This unstable combination shows 2 things: seperate development teams and no focus on SSDT. We’ve sticked for years on Visual Studio 2015 as 2017 proved every time unstable.
As an SSIS developer, I can say that the PQ source will be awesome. Not only the varied number of sources that are now available “out of the box”, but also certain transformations like “unpivot all other columns” which are dynamic by nature will be really helpful in SSIS.
IMHO this feature can overcome the intrinsic limitations of Excel Source component, or at least could ease some of the typical challenges. In some scenarios you have to live with custom Excel files (ok, let’s call them properly – user reports) with no power to change their format or ask for a more tabular source. Using the flexibility of Power Query will help in extracting all the data you need.
In my opinion, this is the first step for killing the current SSIS experience and have a coherent and unified experience in all Microsoft Business Intelligence tools (Excel, Power BI, Azure Data and SSIS). I believe Microsoft will follow the same path it did with VertiPaq engine to have a unified experience in all tools (Excel, Power BI and SSAS Tabular) and will change the current SSIS experience. M Language will, in my opinion, take over of all the integration steps in the future and DAX will be the analytical language. It’s funny because less than a month ago, I wrote a post precisely about the future of SSIS because there was the launch of Power BI Dataflows. Now I see these update and I really start to believe that “one fits all” approach will come to SSIS.
I’ll be crossing all my fingers and toes that this reaches SQL Server 2019 so those of us who aren’t allowed to put our heads in the cloud are allowed to play to.
I suspect people will find a lot of clever uses but for me the two obvious ones that stand out are:
1) Simple(ish) manipulations; the VB like code that goes in to transformations has never been entirely natural to me and breaking out a script task to do anything beyond the basics has always felt like overkill. Starting with the normal Power Query interface and then copying over makes this a lot more accessible.
2) As a few people above have hit on, SSIS doesn’t play nice with variable numbers of columns. Spreadsheets with fixed rows and ever increasing metrics in columns suddently get a lot nicer when you can unpivot in the data source.
If I have a worry it’s performance. If the gap I see between something like loading a folder of Excel files via Power BI Desktop and via an SSIS package run locally holds then we’ll be limited to smaller use cases or some kind of hybrid approach. Perhaps a Power BI data source reading one file with SSIS handling the looping through multiple copies, maybe even in parallel would bridge the gap?
We built a Integration Service using Excel and Power Update to pull data from over a dozen ERP.
This can definitely help us check the Enterprise grade box
I believe this is good news for every Microsoft products user. However, i’m concerned about performance. Will the performance of the SSIS packages created be the same as the PQ performance in Excel/powerbi? I’m just wondering, because if the performance will be the same then the use cases of this new feature can be quite limited…
I don’t know for sure, but yes I guess that performance in SSIS will be the same as in Excel/Power BI. I would not use this for large data volumes.
I was fortunate enough to sit with Sandy Winarko who showed me Power Query in action. I asked him about performance and he said you can scale up the IR to get more performance
Thank you Chris and Gilbert, for your inputs in this matter.
@ Gilbert Quevauvilliers, is there any resource one could use to make sure of that statement you made? I’m just asking this, because it would be a nice “selling point” among devs and management teams who would like to use this technology, and a resource would be helpful to demonstrate the value and the potential.
Thanks 🙂
Hi @ST, as far as I understand Sandy Winarko works in the SSIS team and was responsible for the delivery of Power Query in SSIS. Being fortunate enough to see a live demo from Sandy I asked that exact question and that was his response.
If I had the time and availability to test I would like to try and test it, I have to try and find the time!
Just tested the performance and it sucks. Reading 150.000 rows with SQL takes only 0.9 second, with Power Query it takes 9 seconds.
Good thing instead of having 20 different connectors you have one connector with 20 types of connections. It gives you more focus on the process. Bad thing, will we get one connector that can write to 20 different target types ?
Hi!
Thanks Gil for taking the time.
It’s a pity that there isn’t a resource for this… Hopefully there will be one soon.
Cheers
Chris, thanks for highlighting the availability of this. Love it! Unfortunately it appears that Html.Table is not an included part of the release so far. Really hoping they add it in. It makes the whole web page shredding process so easy.
Just finished an SSIS package scraping tables from EIA. Works awesome! Did the design/transforms in Power BI Desktop, pulled the script and pasted into the Power Query source.
Html.Table is not supported, you can see here a simple web Query will work but not With a inline function (loop)
https://stackoverflow.com/questions/57281861/power-query-in-ssis-issues-with-inline-function-in-query
Thanks for the update Chris. It’s great news, as this really empowers the end user to undertake a degree of data exploration on there own (and data cleansing, ETL)! I.e. moving away from ETL being this techie IT function.
I’ve now trialed this. I’ve installed the latest SSDT on my server, created a data flow with Power Query task, and this executes well within SSIS in Visual Studio 2017 SSDT .
However, my excitement has now been somewhat diminished… I’m now getting errors when running this package when deployed to my SQL Server 2017 instance… Please don’t tell me that I can only deploy this on Azure Data Factory for it to work…
Any thoughts/ideas on how to get this working? Any advice you could give that would allow me to deploy this to an on-prem 2017 SQL server, to schedule the new SSIS package with PowerQuery would be greatly appreciated!
Hi Tim, as far as I know it currently is only supported in ADF. I would imagine it would be coming to SQL 2019, but dont quote me on that!
As the docs say here: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/power-query-source?view=sql-server-2017
“For the current preview release, to facilitate quick feedback-gathering and frequent feature enhancements, Power Query Source can only be used in SQL Server Data Tools (SSDT) and Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF).”
Hi, do you know how long they take to add all the data sources. E.g PDF is available in PowerBI but not available in SSIS
I don’t know, sorry. In other scenarios where Power Query has been integrated with existing tools this has happened gradually.
Hi Chris, does the Microsoft SQL Server Integration Services Connector for Power Query (Preview)(https://www.microsoft.com/en-us/download/details.aspx?id=100619) extend the Power Query Source to be used in SQL server 2017/2019? Meaning that you can schedule an SSIS package with a Power Query Source in SQL?
Working with Active directory data in SSIS is not difficult anymore with the introduction power query in SSIS