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());
            
            try
            {
               wb.RefreshAll();
                wb.Close(SaveChanges:true);
            }
             finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                 if (excel != null)
                {
                    excel.Quit();
                    int hWnd = excel.Application.Hwnd;
                    uint processID;
                    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
                     Process.GetProcessById(Convert.ToInt32(processID)).Kill();
                    Marshal.FinalReleaseComObject(excel);
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

  
         }
        [DllImport("user32.dll")]
        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