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:
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 🙂
I loaded some built-in sample data from Wide World Importers into a Fabric warehouse. You…
It was great being at dataMinds Saturday 2024 this past weekend. A great crowd of…
Today I was having a nice discussion with some colleagues about Fabric and pricing/licensing came…
I recently purchased and read the book Deciphering Data Architectures - Choosing Between a Modern…
A while ago I had a little blog post series about cool stuff in Snowflake. I’m…
I have the pleasure to announce I'll be presenting at two conferences this spring. The…
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