looping a script

[ali_hijazi]

Hello I got to fetch data from several servers that contain the same database schema (i.e. same table names,etc...)

I want to change the connection string on each loop and save each table in a distinct qvd

what I want to parameterize is the following:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=entUAE;Initial Catalog=PS_DEV;User ID=RO;Password=RO;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HANI-SAGHEER;Use Encryption for Data=False;Tag with column collation when possible=False];
F4311:LOAD *;SQL
Select dbo.fn_ConvertJulianToDate(PDTRDJ) as PDDATE, * from testdta.F4311 where PDDCTO in ('OM','OC','OL');

store F4311 into $(vQvdPath)F4311_entUAE.qvd(qvd);DROP Table F4311;

the info in yellow highlight will change according to the loop.

[whiteline]

Hi.

You can use 'for' statement to loop it.

Also you could create an inline table with serverNames to pick one for each iteration with fieldvalue() or peek() functions.

[ali_hijazi]

using peek or fieldvalue works for the load script as follows:

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = 'vDolphin.dbo.' & peek('name',i,'tables');

LOAD *;

sql select * from $(sheetName);

NEXT

but how can I make the connection string in the ODBC Connect or OLEDB Connect dynamic?

[ali_hijazi · Accepted solution]

as for the connection string you can store the connection strings in txt files and inside the for loop you can read the desired txt file using the following command:

$(Include=$(i).txt);

[whiteline]

You can also use the same approach as with $(vQvdPath).