Forum Discussion
How to connect to any available database when primary one is offline
Super UserHi 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 step
Pete
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
Hi BA_Pete,
Thank you very much for your thorough suggestion.
I have tried it and found the same issue as earlier.
Regards,
Min Thein
- BA_Pete
Hi MIN,
This is quite odd. I have no problems switching between sources in the ways that we've discussed here.
Maybe try one more thing:
1) Create a brand new query that only points to Server1, and make any transformations you need. Let's call this 'queryOnServer1'. Disable Load for this query.
2) Create a brand new query that only points to Server2, and make any transformations you need. Let's call this 'queryOnServer2'. Disable Load for this query.
3) Make sure both queries connect and are authenticating in their own right.
4) Create a third query that starts like this:
let Source = try queryOnServer1 otherwise queryOnServer2 ......or like this:
let testSource = try queryOnServer1, pickSource = if testSource[HasError] then queryOnServer2 else queryOnServer1, ...5) Test by then taking Server1 down and see if this works.
I also notice that you don't seem to have any navigation steps in your query, which is where you would normally define the schema and table that you want to import. Not sure if this is relevant, but appears strange. Also not sure what your 'AddSourceColX' steps are doing.
Pete