Power Query in SSIS

This post is also available in:

Hits: 5117

Two years ago I wrote an article on how to export data from Power BI Desktop to an SQL database. In the meantime, several other blog authors such as Leila Etaati from Radacad have discovered the topic and developed it further. Ruth from Curbal has also published a video about it.
However, one point has always disturbed me a bit about the solution: the performance can be very bad with large data sets, which Soheil Bakshi rightly criticized. But now there is another option which can use the possibilities of Power Query in SSIS directly.

If you want to read more about Power BI, subscribe to our newsletter.

Name

Vorname / First Name

Email address:

Leave this field empty if you're human:

Short introduction SSIS

SSIS stands for SQL Server Integration Services and is, in my opinion, a universal, but also somewhat dusty data import tool for Microsoft’s SQL Server. Since Power Query is on the market, my love for SSIS has become smaller and smaller… Sorry Microsoft, but it is like that. The interface is no longer modern, but the possibilities to import data in a certain order are not to be despised. Something that Power Query is still missing.

Microsoft recently announced that Power Query will finally be integrated into SSIS. The articles can be found here and here and of course Chris has already written about it….. Chris, when do you sleep? Whatever…

Power Query in SSIS is finally here!

What else SSIS can do, I refer you to corresponding introductory articles and video trainings, of which there are countless on the Internet (for example in the Microsoft Docs).

Power Query in SSIS

To use Power Query in SSIS, you need three things:

• An installed version of the current SSDT (SQL Server Data Tools)
• An SQL Database (Download SQL Express) or in the German version here.
• Power Query respectively M – knowledge
• Power Query respectively M – knowledge

That’s really all there is to it!

Work Steps in Power Query

At the moment (May 2019) Power Query in SSIS does not have the usual user interface and only the M-code can be captured.
Creates a small query in Power Query, such as importing a csv file.
In the example, I imported the Contoso Products file and did some transformations.

†Power Query in SSIS

Now switch to the SSDT and create a new SSIS package (instructions).

Next, in the new window, a data flow task from the SSIS Toolbox is dragged to the grey area on the right. (Note: Unfortunately, I can’t switch the SSIS Toolbox from German to English, although the rest of the GUI is in English….)

Double-click on the new task in the grey area to open the next window, the data flow. There you can see a new task in the SSIS toolbox: Power Query Source.

Insert M Code

Now double click on the new field on the right and insert the M-Code of the Power Query query here. We leave the query mode on “Single Query”. The other option would be “Single Query from Variable”.

Establishing connections

SSIS always requires information to connect to both the source and the SQL server as the target.
Switch to the Connection Manager. Here we would normally have to enter the entire connection string for the data source and also create a Connection Manager. But not with Power Query!
Since we have already entered all data in the M code, we can let SSIS do some of the work right away. Click on “Detect Data Source”.

Not bad at all. But still we get a warning.

The Connection Manager is still missing. Click in the field “Connection Manager” and select “New Connection”.
And SSIS reads the corresponding file path from the M-code. Automatically. Life can be so beautiful.

Test the connection and if all works, we can now load the data into a new (or existing) SQL table.

Create a SQL Table

For this we need a Destination Assistant (1), then select the SQL server (2) as an option and click on “New” (3). The connection manager appears, where we enter the SQL Server (4) and the database (5). Test the connection (6) and confirm the message with “OK” (7).

Now you have two objects on the screen. Drag the blue arrow from “Power Query Source” to “OLE DB Target”. By the way, you can assign a different name by clicking on the tasks and using F2.

Since we still have a red cross at the target, we open this with…. Double click, what else…

We are still lacking a table, which SSIS also communicates to us.

Click on “New”. SSIS gives us a suggestion on how to create the new table with a T-SQL statement.

And the great thing is… SSIS has already converted the data types from the M code into SQL data types. So we don’t have to do any more tedious transformation steps. Life can be so beautiful.

Click “OK” and the table will be created in the SQL database. Unfortunately a confirmation message is missing, but the table is really created.
And as a last step we tell SSIS which column from the M script belongs in which SQL table column. But SSIS helps us here, too, and tries to make the corresponding assignment itself. Switch to the “Mappings” tab.

Below you can see the input columns from the M code on the left and the corresponding SQL table columns on the right. Click on “OK” and the red cross on the “OLE DB target” disappears. If the mapping is not correct, you can change it here by clicking on the appropriate column below and selecting the correct entry from the dropdown list.

Testing the solution

And now we are testing the whole thing and run the SSIS package. At the top in the middle you will find a green arrow which starts the solution. Click once on the arrow.

After a short time (in this case it was 2,156 seconds…), SSIS extracted the data from the csv file, converted it and stored it in the SQL table. Life is good!

As a proof, a screenshot from the SQL Server Management Studio.

Conclusion and outlook

In this article I showed how Power Query makes life easier. Thanks to the Power Query Task, it is now possible to first develop solutions in a Power Query Editor and then use SSIS to store the data in an SQL database. After deployment of the SSIS package, the solution can be used again and again.

Tedious tasks in SSIS can also be solved very easily by using M code. Furthermore, the whole orchestral possibilities of SSIS can be used, such as the sequential import of queries in a defined order.

Or the SSIS packages can be controlled with the SQL Server Agent Jobs at a specific time. Or they can be started with stored procedures from outside (e.g. from… Power Query).

Also, cumbersome workarounds like finding a Power BI desktop port number are no longer necessary in every case.

What’s certainly missing for me at the moment are two things:

• The Power Query interface in SSIS – at the moment the M code must be inserted, which is certainly not suitable for every user.
• The possibilities of SSIS for incremental data import into the Power BI dataflows…. But one may dream.

In the end there is only one thing left for me: Now I like SSIS again.

You can read more about Power BI here.

Happy Querying!

If you want to read more about Power BI, subscribe to our newsletter.

Name

Vorname / First Name

Email address:

Leave this field empty if you're human: