After a careful research and help from colleagues, I realised that the way I have structured the question is wrong. What I wanted to do is create a dimensional model, where one of the dimensions are the customer details from 2021 to 2100. Whenever new data is added, the model must be automatically updated. I am doing this project on DI Studio. To load the raw data from 01/2021 to 11/2021 (and beyond), I used a control table with all the months from 01/2021 to 12/2100 as follows: CCYYMM 202101 202102 202103 202104 202105 202106 202107 202108 202109 202110 202111 202112 202201 ... 210012 Then, I used this control table to create a parameterised (macro) job with the following parameter (macro variable): yearmon with default value: 202101 This is both a parameter in the job, and in the source file, which is named: CUSTOMER_HIST And located somewhere like that: C:\source\&yearmon\customer_hist_&yearmon..csv Where &yearmon, as defined, takes values 202101, 202102,....,202111. The diagram of the job is as in the image. Please make sure: 1. In the Properties of the raw file, beneath File Location tab, select "Double quotes around file name". 2. Define the parameter in the Parameters tab of the Properties of the raw file. 3. In the Table Loader Properties, Load Technique Tab, select: Append to Existing. After I ran the macro job, I created a loop job, where I ran the whole loop of the values contained in the control table. The diagram is as follows: 1. Notice the macro job included, noted with the "&" symbol. 2. Open the loop tab, and match the created parameter/ macro variable with the variable of the control table. The final output table CUSTOMER_HIST is as follows (a sample): Cutomer_ID DATE FName LName Gender Date_of_birth Postcode Address Alive_flag 38962 01JAN2021 John Smith M 23MAR1964 BS2 1EG 18 Clifton Road, Bristol, England, UK 1 35688 01MAR2021 Sarah Williams F 03APR2008 LA1 7TY 1 Manchester Road, Lancaster, England, UK 1 ... 56789 01NOV2021 Georgia O'Sullivan F 03AUG1996 BT5 8UI 22 Church Lane, Belfast, Northern Ireland, UK Note: the examples above are imaginary and just came out of my head. Any similarity with the reality is coincident. I don't even know if the addresses above exist in the respective towns,
... View more
