This post is also available in: German

Hits: 11034

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.


11 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
  • 22. July 2019 at 21:33
    Permalink

    I have tried this for Salesforce without luck. I think my issues is with the connection manager not having a specific type for Salesforce. Web would be the next best option but I get a credential error even with providing my username and password that work with Power BI/Excel connectors. Is this even possible in SSDT?

    Reply
    • 30. September 2020 at 10:15
      Permalink

      Hi Mike. Thx for your question. I haven’t tried that and I do not have access to Salesforce, so I can’t try it. Sorry.

      Reply
  • 28. July 2019 at 7:33
    Permalink

    Great one.. I have tried and all worked fine when triggered manually from SSDT. But while trying to deploy the package to SQL, it give the error as “Unable to create the type with the name ‘PowerQuery’”.

    Any pointers on how to solve it ?

    Thanks

    Reply
  • 30. July 2019 at 15:11
    Permalink

    I wanted to use this approach, but I have a Power Query with a function that I use to loop over a column and those type og M codes don’t work in SSIS, have you try that yourself?

    Reply
  • 30. July 2019 at 23:57
    Permalink

    Lovely solution but I had the same issue as Dmitry: my package works perfectly in VisualStudio but when I deploy it into SQL it won’t execute because the connection created by the “Power Query Connection Manager” is not deployed with the package. (Exstension SSIS 3.1, 7/24/2019)

    Reply
  • 30. September 2019 at 2:22
    Permalink

    Thank you for this great blog. I have an issue with the Web service. I am using a public link from BOX, ButI keep getting an error saying ” Credentials are required to connect to the web service”. I have confirmed that the link is public and doesn’t require any authentication. I even used it on a browser and the file gets downloaded right away. Any suggestion?

    Reply
    • 30. September 2020 at 10:14
      Permalink

      Thx for your question, Adam. Unfortunately, I do not have access to BOX, so I can’t replicate your issue.

      Reply
  • 4. March 2020 at 12:44
    Permalink

    Hi Hans! Thanks for the tutorial.

    I have a slight dilemma that I would love to get some help with.

    I want my Power Query Source to get data from a web API. I need to send the authentication info in the headers of the request in the M-code which is not a problem when I have that M-code in Power BI.

    The problem is when I try to use that code in SSIS. It demands a connection manager(CM) to be connected to the data flow component. Fair enough. In the CM I set authentication to anonymous since I already sent in the username and the password with the headers in the M-code.

    That doesn’t work. In the PQS component I get the following error: “Credentials are required to connect to the Web source.”.

    I’m completely at a loss for what to do at the moment. Been scratching my head over this for a few hours now and any help you can offer would be lovely!

    Reply

Leave a Reply

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

%d bloggers like this: