Forum Discussion
How to connect to any available database when primary one is offline
Super UserHi MIN ,
I don't see the issue here, it's just asking you to confirm the credentials isn't it?
Confirm the credentials and these will be saved in the PBIX Data Source area, then the switch should work fine.
Pete
Hi BA_Pete ,
Actually, I'm trying to connect to any of 2 identical databases hoping that 1 of them is accessible in case the latter is offline.
For that reason, I shut down the db1 (Source1) -
to test if "try" statement catchs the error (offline status) and routes to Source2.
Instead, db1 prompts again to provide credential when it goes offline.
On the other hand, I'm not sure it is a correct way to setup a dynamic connection on PBI.
Regards,
Min
- BA_Pete
Hi MIN,
I wonder whether adding a step below each source will force a recognisable error to initiate the switch. Try something like this:
let Source1 = SQL Server 1, chgTypes1 = Table.TransformColumnTypes(Source1,{{"numberColumn1", Int64.Type}}), Source2 = SQL Server 2, chgTypes2 = Table.TransformColumnTypes(Source2,{{"numberColumn1", Int64.Type}}), pickSource = try chgTypes1 otherwise chgTypes2, xformSteps = // the rest of your code, using pickSource as the previous step in the first stepPete
Hi BA_Pete,
Thank you for your time.
It seems like "try" do not skip whenever the expression is valid and prompts for credential.
Regards,
Min
- BA_Pete
Hi MIN,
You've not added a new step in your screenshot, you've just surrounded your original connection string with the TransformColumnTypes and AddColumn functions. Please try it exactly as per my example, using a column that is known to exist in the table when the source is connected, not by creating a new one. Creating a new column to transform like this completely negates the point of the process as it won't throw a 'column not found' error, it will defer to the source credential error.
Try going through it like this:
1) Bring both sources back online.
2) Create a connection to Source1 and provide valid credentials if prompted.
3) Add a new custom column called 'Source' with the value "Source1".
4) Pick any column in that table and change the data type to create the chgTypes1 step.
5) Add your Source2 connection and select this step in the Applied Steps list to load. Provide valid credentials if prompted.
6) Add a new custom column called 'Source' with the value "Source2".
7) Pick any column in that table and change the data type to create the chgTypes2 step.
8 ) Add a custom step with the pickSource step code.
9) Check the [Source] column to see which source is being loaded from at this point.
10) With the pickSource step still selected, take Source1 offline and Refresh Preview.
11) Check the [Source] column to see which source is being loaded from at this point.
The only other way I can think of to force the switch would be to change the error evaluation and switch at step 8 ) above slightly by adding a separate testSource step and changing the pickSource step, something like this:
let Source1 = SQL Server 1, chgTypes1 = Table.TransformColumnTypes(Source1,{{"numberColumn1", Int64.Type}}), Source2 = SQL Server 2, chgTypes2 = Table.TransformColumnTypes(Source2,{{"numberColumn1", Int64.Type}}), testSource = try chgTypes1, pickSource = if testSource[HasError] then chgTypes2 else chgTypes1, xformSteps = // the rest of your code, using pickSource as the previous step in the first stepHopefully going through the steps like this will get this working for you. If not, then it may be an issue with how the sources are set up on your gateway(s) preventing the correct credentials from being used.
Pete