An alternative to using SQL Server Integration Services (SSIS) to get data into your database is a tool called Power Query. In this article by Matt Allington, we learn how to use Power Query to get data into your database.
Now before I get a million comments from professional SQL Server experts, I am not suggesting that everyone should swap out SSIS for Power Query. I understand the importance of an enterprise strength tool like SSIS and I know it is a great tool for that purpose. I am just talking about people like me that work in the Self Service BI/Excel space, use SQL Server as a tool, but have only limited opportunities to work with SSIS. This is my situation, and if this also sounds like you then Power Query is a great alternative.
The benefits of Power Query over SSIS include:
- Power Query is easy to learn
- It is UI driven so you don’t need to understand the code (although it can help. You just point and click.)
- Importantly it is easy to come back 3 months later and make changes without needing to remember how the software works.