Categories: Power BISSIS

Thoughts on Power Query and SSIS

Recently I received a very interesting question on Twitter from Jeremy (blog | twitter):

His question was a response on my earlier blog post What’s the deal with Excel and SSIS?, where I remarked that Power Query will rescue the day since SSIS has a lot of issues with Excel (or rather the JET/ACE OLE DB providers have a lot of issues).

I really believe Power Query is a lot more powerful in handling Excel than SSIS. Why? I’ll give you a couple of reasons:

  • ever got a 32/64-bit error in Power Query when reading Excel? I don’t think so.
  • Power Query gives you a nice overview of all the present sheets and tables in the Excel workbook
  • in Power Query, you have a WYSIWYG editor showing you the data from the Excel workbook and the result of the transformations you applied
  • a lot of transformations are a lot easier in Power Query than in SSIS. Try a (un)pivot transformation in SSIS and then in Power Query. See how easy that went?
  • there are transformations available out-of-the-box in Power Query, where you would have to write .NET code in SSIS. An example is the fill down transformation, of which I give an example in the tip Introduction to Power Query for Excel.
  • So far I have seen less issues with Excel and data types in Power Query

Power Query takes a lot of the woes that SSIS has with Excel away. It makes it easier and more intuitive to import Excel data. There is no query folding in Power Query for Excel but neither is there in SSIS.

SSIS on the other hand is better in control flow stuff: looping, scheduling, e-mailing and so on. The problem with Power Query is the manual work in Excel. Meaning, you have to create the query in Excel and you can either refresh the query manually or you can upload it to the Power BI online environment. However, the good news is Power Query will be integrated into SSIS! That’s right, in SQL Server 2016 Power Query will also be part of SSIS, giving you the best of two worlds: the transformation awesomeness of Power Query and the mature control flow power of SSIS. Another reason why this is such great news is that Power Query supports a gazillion more sources than SSIS.

The future is bright for us ETL developers 🙂


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

  • While using Power Query to prep Excel or other data for consumption by SSIS is possible, it sounds like it is not yet automatable. For example, in those cases where I need to automate the import of Excel data into SQL Server, Power Query can't be in the middle because it still needs to be manually refreshed?

    • Hi Jeremy,
      it's true, Power Query needs to be manually refreshed and is not automatable out of the box.
      However, you could refresh the Excel workbook using a script task. Matt Masson wrote about this:

      Refresh an Excel Workbook with a Script Task

      So there is a workaround for earlier versions of SSIS. However, full automation out of the box is foreseen in SSIS 2016.

      Koen

Recent Posts

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

2 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

6 days ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

7 days ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

1 week ago

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

3 weeks ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

1 month ago