Forum Discussion

PolinaOr's avatar
PolinaOr
Helper I
3 months ago
Solved

Dataflow passing validation check, but has hidden errors

Hi, everyone

 

I am trying to connect to my personal OneDrive Document and organisational Lake Dataset.

 

I have tried using Dataflow recently, which actually worked 2 times when loading it (out of 10), but stopped passing validation step. In this case Preview was working well, no errors shown.

 

I re-did the whole data flow from scratch, it initiated login screen AND gateway screen, where I have created same gateway type I used before, but just linked to my account, preview operates well again, no errors in logic or steps are being shown, and validation is being passed. But I am getting Run Error.

 

Re-did the workflow second time, it didn't launch the gateway screen. All the same stuff remained - Preview perfect, validation passed, run not finished.
It forced me to check the credentials and connections to the organisational Lakehouse, which were in fact not working. So when I was trying to correct them, I was getting following error.

I have also run the same query set in just plain Excel Power query, which gave me an actual connection error, also below.

Gateway error? in PQ

Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

All this is weird in itself due to several reasons:

 

1) PQ will actually give out a connection error (even though PQ doesn't require gateway connection).

2) I had 3 instanced of gateways.

a) Not marked for login and worked, probably not "mine" - didn't check back then, as query managed to load twice
b) not mine, marked for login and didn't work
c) "My" gateway (which I might be completely misunderstanding), no login screen, not working

 

So overall apparently the issue seems to be connected to gateway, but I need to be sure about that so I can contact our BI team

 

General Comment
Need Help
  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    3 months ago

    Hi PolinaOr,

    Thanks for reaching and outlined the steps.

     

    The issue is related to the permission on the gateway connection, cna you try to do below things?

    1) connect the lakehouse in SSMS which was used in the connection "Lakehouse LH_Gold" which you can find from the gateway settings.
    If connection is working in the SSMS or not please confrom.
    2) If not you can ask BI Team/Admin to add your user in the lakehouse and giving you the correct access. Then you try to connect this lakehouse with the connection mentioned.

     

    Please consider as an accepted solution if helps or give some kudos.

2 Replies

  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    Solution Sage
    3 months ago

    Hi PolinaOr,

    Thanks for reaching and outlined the steps.

     

    The issue is related to the permission on the gateway connection, cna you try to do below things?

    1) connect the lakehouse in SSMS which was used in the connection "Lakehouse LH_Gold" which you can find from the gateway settings.
    If connection is working in the SSMS or not please confrom.
    2) If not you can ask BI Team/Admin to add your user in the lakehouse and giving you the correct access. Then you try to connect this lakehouse with the connection mentioned.

     

    Please consider as an accepted solution if helps or give some kudos.

    • PolinaOr's avatar
      PolinaOr
      Helper I
      2 months ago

      Hi,

       

      I have contacted BI team and they granted me access. It works now when loading basic data, but I have to essentially now check where exactly dataflow is failing. I will be building steps up to see where it breaks.