Perfect. We're almost there. Can you attached the CREATE TABLE code that's used for each of the first 3 groups?
The create table sql code gets automatically generated when I go to the edit property of the OLEDB destination-> New(table load or fast view). The only thing I do for every new table is add an ID column as shown below
CREATE TABLE FILENAME1_GROUP1 (
id_num INT Identity (1,1),
Column1 varchar (800)
Column2 varchar (800)
ColumnN varchar (800)
Also, you mentioned the "text qualifier" in your Step 3. Are these files comma separated and all columns are "double quoted" or just some of the "cells" are "double quoted"? And which version of SQL Server are you using? 2016 or less or 2017 or greater?
I believe some of the column could be double quoted which is why I specify the column delimiter. To be honest, I am not sure what the Column Delimiter field does and how adding ' " ' affects the table. I am using SQL Server 2019
The other steps I perform is I set the column length for each column as varchar(800). I believe this is to prevent truncation of certain text columns; and, in the advanced property of the FLAT FILE Source, I add the FileNameColumn in Custom Properties in the Component Properties tab
Hope that helps