This post is also available in: de

Hits: 3675

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.


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:

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.

M-Code Import Contoso-Products
M-Code Import Contoso-Products

†Power Query in SSIS

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

Creating an SSIS Package
Creating an SSIS Package

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….)

Creating a Data Flow Task
Creating a Data Flow Task

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

Power Query Source in the Data Flow
Power Query Source in the Data Flow

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”.

M-Code in SSIS
M-Code in SSIS

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”.

Create connection to csv file
Create connection to csv file

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

Connection Manager still missing
Connection Manager still missing

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.

Creating Connection Manager
Creating Connection Manager

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).

Creating SQL Destination
Creating SQL Destination

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.

Connect Target to Destination
Connect Target to Destination

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.

Create new SQL Table
Create new SQL Table

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

CREATE TABLE
CREATE TABLE

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.

Mapping
Mapping

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.

Start SSIS Package
Start SSIS Package

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!

Success!
Success!

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

Data in the SQL Table
Data in the SQL Table

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.


3 thoughts on “Power Query in SSIS

  • 4. July 2019 at 16:40
    Permalink

    Great info!
    however, we cannot deploy this to ssas with error “Unable to create the type with the name ‘PowerQuery'”
    how to deal with it?

    Reply
    • 4. July 2019 at 21:43
      Permalink

      Hi Dmitry
      Thank you for your comment.
      I deployed the solution from SSIS to SQL and it worked in my case.
      I did not try it using SSAS, so I cant say anything about it.

      Reply
  • 4. July 2019 at 16:41
    Permalink

    Great tutorial!
    however we cannot deploy this to ssas with error “Unable to create the type with the name ‘PowerQuery'”
    how to deal with it?

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: