A workaround to use Power Query with SSIS Script Task

M is quite a nice language for Self Service ETL. The logical next step would be to include the Power Query functionality as a component in SSIS. As of now there is just the following Workaround. (Which is btw not supported but works quite well.)

Create an Excel Workbook that contains the M-query and loads the data to a worksheet. Make sure that the data fits in a worksheet. It will not work with the described steps if you load the data to the data model.

I discovered and implemented this blogpost from Jessica Moss: http://jessicammoss.blogspot.co.at/2008/10/manipulating-excel-spreadsheets-in-ssis.html but always got this error:

“Exception has been thrown by the target of an invocation.

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript


The problem was that the Excel Process is not killed properly so the quick & dirty solution is to kill the process:  

    public void Main()
            // TODO: Add your code here
            Microsoft.Office.Interop.Excel.Workbook wb;
            var excel = new Microsoft.Office.Interop.Excel.Application();           
            //wb = excel.Workbooks.Open(@"D:\myWorkshops\Power Query Stock Analyse\PriceDownloadV1.xlsx");
            wb = excel.Workbooks.Open(Dts.Variables["FilePathToRefresh"].Value.ToString());
                 if (excel != null)
                    int hWnd = excel.Application.Hwnd;
                    uint processID;
                    GetWindowThreadProcessId((IntPtr)hWnd, out processID);

            Dts.TaskResult = (int)ScriptResults.Success;

        private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);   

Don’t forget to add a reference to the Microsoft.Office.Interop.Excel; Version 14 Assembly. (Might also work with Version 15).

The rest is simple SSIS: create a DataFlow Tasks with an Excel Source pointing to your Workbook.

SSIS and Powerquery

Comments (2)

  1. Mike says:

    "Quick and dirty", but it works - hooray!

    Thanks for posting this - very helpful.

Skip to main content