I have a situation in which I must create an entire DB off of ~150 external txt files (pipe delimited) which all represent unique tables. Furthermore these txt files come in daily and need to be loaded (with correct types) into this internal DB. The files do have filenames that correspond with the table names and they also have header rows.
Creating 150 different flat file connection strings, data conversions, and data flow tasks in SSIS does not sound plausible. Thus I am considering doing a dynamic TVP load (a hybrid of this: What's the most efficient way to load a csv file into SQL Server 2016?) via c#, where I would dynamically set the table name and columns based on the metadata provided in the txt files, then I would pass these parameters along with the data (one row at a time leveraging a TVP parameter) and insert the data into the dynamically set table.
My question is, has anyone dealt with the task of ingesting an entire DB export via flat files and recreating it locally in their environment? If so, how did you do it?