asked
process multiple files
I have a Customer XYZ , it has two sources one is FTP and other is excel, FTP file has some reference data in Excel file , so whenever we load two files we do a merge join on Code which is common between two files and load it into a destination. @Data Flow level: FTP File 1 merge join on code with Excel file1 The complexity comes when XYZ has multiple FTP files and multiple excel files; what should my approach be in order for me to load FTP File 1 with excel file 1 , FTP file 2 with Excel file 2 , FTP file 3 with Excel file 3 …. Or how can I relate FTP File 1 with excel file 1 , FTP file 2 with Excel file 2 , FTP file 3 with Excel file 3. I don’t want to process FTP file 1 with excel file 3 or FTP file 2 with excel file 1 @Data Flow level: FTP File 1 merge join on code with Excel file1 @Data Flow level: FTP File 2 merge join on code with Excel file2 @Data Flow level: FTP File 3 merge join on code with Excel file3 and so on….. Please do let me know Thanks
you could use a for loop which executes that Data Flow Task for excel file 1 and FTP 1. Use variables for the Connection strings of the FTP file and the Excel file. After the first transformation update the variables (connection string) using script task, to read the FTP2 and the ExcelFile2. Similarly do it for all the file.You need to set the for loop according to the number of files in both type. Hope the number of files in each type is equivalent.
