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'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…
In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…
I wrote a piece of SQL that had some new T-SQL syntax in it: IS…
I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…
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