I'm receiving an error when using the Power Query Source within an SSIS data flow. I'm specifically using the web connection, wanting to import data from an HTML table on the website. Here is an example query, which is copied straight from Power BI desktop (where it works fine, and returns the data table):
let
Source = Web.BrowserContents("https://learn.microsoft.com/en-us/sql/integration-services/data-flow/power-query-source?view=sql-server-ver15"),
#"Extracted Table From Html" = Html.Table(Source, {<!-- -->{"Column1", ".card-content-title *"}, {"Column2", ".card-content-description"}}, [RowSelector=".has-margin-bottom-large .margin-top-xs"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{<!-- -->{"Column1", type text}, {"Column2", type text}})
in
#"Changed Type"
The Power Query Source accepts this query, I can create the web connection, and the connection test is succesful. The error happens when I either click on the Columns page, or click OK to close the Power Query Source window. I'll copy the full error text below (sorry, it is very long). I'm hoping someone can help decipher this error text.
Error message:
TITLE: Microsoft.SqlServer.DTSPipelineWrap
------------------------------
We're sorry, an error occurred during evaluation.
------------------------------
ADDITIONAL INFORMATION:
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified (Microsoft.MashupEngine)
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
The system cannot find the file specified
------------------------------
BUTTONS:
OK
------------------------------
Error detail:
===================================
We're sorry, an error occurred during evaluation. (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.SqlServer.IntegrationServices.PowerQuery.UI.PowerQuerySourceUI.ConnMgrsPage_ConnectionManagersPageSaved(Object sender, EventArgs e)
===================================
The system cannot find the file specified
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation`1.TryCompleteWithException(Exception exception)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.EvaluationThread(Object state)
at Microsoft.Mashup.Evaluator.GlobalizedEvaluatorThreadPool.<>c__DisplayClass1_0.<Start>b__0(Object s)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__1(Object o)
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)
===================================
The system cannot find the file specified (Microsoft.MashupEngine)
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.RemoteEvaluationContainerFactory.Container.OnException(IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0`1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitForT
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
at Microsoft.Mashup.Common.ChunkedInputStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.Mashup.Common.VirtualStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
at System.IO.BinaryReader.ReadInt32()
at Microsoft.Mashup.Evaluator.ITableSourceSerializationExtensions.ReadITableSource(BinaryReader reader)
at Microsoft.Mashup.Evaluator.RemotePageReader.CreatePageReader(OwnedStream stream)
at Microsoft.Mashup.Evaluator.RemotePageReader.CreateProxy(IEngineHost engineHost, IMessageChannel channel, ExceptionHandler exceptionHandler)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.DataReaderSourceRemoteEvaluation.GetResult(Boolean enableFirewall)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass11_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePageReader.RunStub(IEngineHost engineHost, IMessageChannel channel, Func1 getPageReader) at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass10_0.<OnBeginGetDataReaderSource>b__0(EvaluationResult2
1 result)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_11.<OnBeginGetResult>b__0() at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action) at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action
1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetDataReaderSource(IMessageChannel channel, BeginGetDataReaderSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_01.<AddHandler>b__0(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel) at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0
1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__1(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.Interface.EvaluationResult2`1.get_Result()
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass10_1.<OnBeginGetDataReaderSource>b__1()
at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass1_1.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass4_0.<BeginGetResult>b__0(EvaluationResult21 result) at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action
1 callback)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action1 callback) at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResult[T](IDocumentEvaluator
1 evaluator, DocumentEvaluationParameters parameters)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResultT
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetDataReaderSource(IMessageChannel channel, BeginGetDataReaderSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_01.<AddHandler>b__0(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel) at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0
1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__1(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.Interface.EvaluationResult21.get_Result() at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.<>c__DisplayClass4_0.<BeginGetResult>b__0(EvaluationResult2
1 result)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action1 callback) at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action
1 callback)
at Microsoft.Mashup.Evaluator.Interface.IDocumentEvaluatorExtensions.GetResultT
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResultT
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetDataReaderSource(IMessageChannel channel, BeginGetDataReaderSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_01.<AddHandler>b__0(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message) at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel) at Microsoft.Mashup.Evaluator.MessageHandlers.<>c__DisplayClass3_0
1.<AddHandler>b__0(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass3_0.<CreateThreadStart>b__1(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
===================================
The system cannot find the file specified
------------------------------
Program Location:
at System.Diagnostics.Process.StartWithCreateProcess(ProcessStartInfo startInfo)
at System.Diagnostics.Process.Start()
at Microsoft.Mashup.Engine1.Library.WebBrowserContents.WebBrowserContentsModule.BrowserContentsFunctionValue.GetPageSource(String url, WebBrowserContentsOptions options)
at Microsoft.Mashup.Engine1.Library.WebBrowserContents.WebBrowserContentsModule.BrowserContentsFunctionValue.TypedInvoke(TextValue url, Value options)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue23.Invoke(Value arg0, Value arg1) at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Runtime.DelegatingFunctionValue.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Runtime.DelegatingFunctionValue.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field) at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.Instruction.Execute(MembersFrame1& frame, Instruction[] instructions) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field) at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field) at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame) at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index) at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field) at Microsoft.Mashup.Engine1.Language.Linker.LinkedModule.SharedRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field) at Microsoft.Mashup.Engine1.Language.Linker.AssemblyRecordValue.get_Item(Int32 index) at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name) at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3
4.Invoke(Value arg0, Value arg1, Value arg2)
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction.Execute(MembersFrame0& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.ListInstruction.RuntimeListValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.ListValue.get_Item(Value key)
at Microsoft.Mashup.Engine1.Runtime.Library.List.ElementWithListCheckFunctionValue.Invoke(Value collection, Value key)
at Microsoft.Mashup.Engine1.Language.FunctionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction1.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Runtime.RecordValue.get_Item(String field)
at Microsoft.Mashup.Engine1.Language.FieldAccessInstruction.Execute(MembersFrame0& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction0.Execute(MembersFrame0& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()
at Microsoft.Mashup.Engine1.Runtime.FunctionValue.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Engine.Microsoft.Mashup.Engine.Interface.IEngine.Invoke(IValue function, IValue[] arguments)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)