2

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?

2

BIML is designed for this kind of semi-repetitive task. One defines a "template" that includes placeholders for the file and table names. The tool will then generate the corresponding 150 SSIS jobs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.