Connect Mulitple servers

[rajaqliks]

Hi

I need one help, i have a requirement

I need to  connect dynamically Multiple servers to load the data from SQL server.

I have a code to load data from one server multiple databases but,

I want to load multiple databases from multiple servers please give some suggestions.

Thanks in advance.

[dapostolopoylos]

You will have to define different connections for each server and load data serially...

[Miguel_Angel_Baeyens]

Use the CONNECT command in the script to connect to as many databases as you need. Remember that every CONNECT causes the previous to DISCONNECT, even if you don't write it in the script. So CONNECT to database1, read all the tables, then CONNECT to the next and so on.

[rajaqliks]

i want to load dynamically, the follwoing script i am using to load data from one server.

Variable:

LOAD

     vServerAddress,

     vDataBase,

     vUsername,

     vPassword

FROM

Excel file;

Let vVar=NoOfRows('Variable');

for i=0 to NoOfRows('Variable')-1

Let V1=i;

let vServerAddress = peek('vServerAddress',0,'Variable');

let vDataBase = peek('vDataBase',i,'Variable');

let vUsername = peek('vUsername',i,'Variable');

let vPassword = peek('vPassword',i,'Variable');

CONNECT TO [Provider=SQLOLEDB;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);Pwd=$(vPassword);];

Script;

next i;

[Miguel_Angel_Baeyens]

So you already got it. After you connect, and before looping to the next database, LOAD whatever tables you need, rename fields, transform or store, and same for the next load.

[rajaqliks · Accepted solution]

i am using the following script, one server it will work fine but, if the second server starts automatically it gives a pop to enter username and password.

Variable:

LOAD

     vServerAddress,

     vDataBase,

     vUsername,

     vPassword

FROM

Excel file;

Let vVar=NoOfRows('Variable');

for i=0 to NoOfRows('Variable')-1

Let V1=i;

let vServerAddress = peek('vServerAddress',0,'Variable');

let vDataBase = peek('vDataBase',i,'Variable');

let vUsername = peek('vUsername',i,'Variable');

let vPassword = peek('vPassword',i,'Variable');

CONNECT TO [Provider=SQLOLEDB;Server=$(vServerAddress);Database=$(vDataBase);Uid=$(vUsername);Pwd=$(vPassword);];

Script;

next i;

[Miguel_Angel_Baeyens]

Then you need to make sure that username and password are correct in the excel file, and also that the server allows such connections. It could be that the server only accepts Windows users (NTFS) and not external credentials.

[rajaqliks]

i am using the correct credentials but i am facing the issue. if one server multiple databases it will work fine but if it is two servers multiple databases it is giving the pop up.