Archive

Archive for the ‘BIML’ Category

Power Query with SSIS OData Feed Publishing Components

November 6, 2014 Leave a comment

Power Query won’t be able to connect to Oracle / IBM DB2 database unless the driver is installed locally. In many cases, we cannot freely install the driver due to enterprise security policy and lots of other reasons. And to mass distribute the diver to client PCs is another big issue. SSIS Data Feed component removes this gap via exposing complex ETL as a single SQL Server database view which can be queried by Power Query directly. With SSISDB introduced in SSIS 2012, powerful auditing and performance logging can be enabled with the help of out of box reporting features as well as lots of nice CodePlex community projects.

In a recent project, I use this technic to enable a scenario that an end user is able to query a SQL Server database view which actually invoke an ETL job in real time and streaming data back to Excel. If you do have the Power BI subscription, you can even share the Power Query through Power Query Data Catalog and become searchable by colleagues. To take further, I build an ASP.NET as a query register and capture some metadata of the query. Then I use Varigence Mist (BIML IDE, a super powerful tool!) to dynamically generate the SSIS packages on the fly and deploy to server via Power Shell and create a view on top of it. It is truly awesome and I’m proud of what I achieved.

The only drawback is that the Project Deployment does not support incremental deployment at the moment. I wish in next SSIS release, the incremental deployment can be implemented so that we can add additional SSIS Data Feeds without redeploy the entire project.