sql server – ADF Copy Dynamic Datatype Change in Loop for multiple table

AFAIK, if your schema is same for all the master tables then it will automatically Change the data type to string if you specified it in mapping.

If you are creating file dynamically it will automatically store the data as string when it is (CSV or txt file)

enter image description here

IF schema is not same for all tables, then as @Nadeem said create SQL file with all your SQL statements including conversion/ casting of timestamp column and add it into ADLS.

Convert() function: To convert a datetime to a string, you use the function as follows:

SELECT id, event_name,CONVERT(varchar(50),event_datetime,126)  as event_datetime FROM sample_table2;

enter image description here

then lookup the file using lookup activity

Lookup Output:

enter image description here

and loop over it with each query to copy the table with conversion on timestamp column.

enter image description here

Copy activity source setting:

enter image description here