Gateway Connection Cleanup/Remove unused data source connections

[dbrandone]

Hi everyone,

In the service under dataflows that I created, my gateway is showing "Not configured properly", but refreshes are going through correctly. I checked the dataflows and all are connected to our SQL server using the same connection. I went through all reports that could be connected to this SQL server and none are using the connections in the picture below that are "Red"(Not connected). One of the unconfigured connections is a duplicate of the configured correctly connections (Top one). How do I remove these connections so that only the first one (green) is visible?

[Watsky]

Give this a try. Go into your dataflow editor then just click save. Let it validate then check your data sources. It should remove the phantom data sources.  If that doesn't work then you'd need to delete the source like @edhans stated. 

[edhans]

Do you have connections to those red SQL tables, but the data isn't loaded? That would cause this issue. Look for tables in Power Query not loaded that are at the end of a chain in the Query Dependency View. Those should be removed.

[dbrandone]

@edhans This data source connection is related to dataflows created in the service. When I go to Power Query for the various tables in the dataflow, and then go to "View", I do not get a Query Dependency option to look at. I went into the power query advanced editor of each table in the dataflow and they all reference the same connection (the green, configured correctly one) and all refreshed on all tables have been running fine.

[edhans]

Ok, so Power Query online. I am not sure then. You may need to contact support. There could be some connections stored in the metadata when the dataflow was originally done and isn't being cleaned up properly.

The other option is to delete and recreate the dataflow by copying your existing and clean M code between them. I am not sure how much trouble that is - if one report, probably faster than calling support. If 10 reports rely on it, then support might be the better option.

[dbrandone]

@edhans and @Watsky ,

I went into the power query editor for the dataflows and then saved immediately. The sources are still showing. I have 21 tables in the dataflow (Single Source, but different tables, Our SQL server has 200+ tables). I checked the m language for each power query per table and all are referencing the same connection (the green one). I can't find any connection in the dataflow that references the other connections. I think this is why refreshes have been solid and no refresh errors. This stinks because I have 10-15 reports that pull from this dataflow so reimplementing the dataflows would be labor intensive. 

[Watsky · Accepted solution]

Try one more thing. When you go into your Dataflow editor head over to the options then Project Options. 

In your project options set your gateway to none then click ok.  You'll see a message that tells you that credentials are required to connect to the source. 

Now head back to project options and change your gateway from none back to your gateway. The message will still tell you that credentials are required to connect. So click on the configure connection button then click connect. Make sure that all of your queries icons are back to table icons and not:. 

Save and check the credentials section again. See if that does it for you. If it doesn't work then export the JSON file for the Dataflow 

Start a new dataflow in a different workspace and do Import Model

It'll need you to configure credentials then save it. Check to see if you see it there. If so then there is something in the actual JSON that is causing it. Not a solution yet but it'll tell you whether the actual file is holding the info for the phantom connection.

[dbrandone]

@Watsky 

That worked. Thanks. Only the one datasource is showing. 

The first steps in the process with removing the datasource in project options is what did it. Once I removed and then reapplied, it worked well

[Watsky]

@dbrandone awesome! Sorry the first route didn't work for you. It worked for me but I guess something else was causing it to hang up. Glad it's all better now though!