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

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

5 days ago

dataMinds Connect 2024 – Session Materials

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

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago