Recently I received a very interesting question on Twitter from Jeremy (blog | twitter):
@jmarx definitely yes
— Koen Verbeeck (@Ko_Ver) July 10, 2015
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 🙂
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