Sql-server – 150 External flat files used to create internal DB

cetlsql serverssis

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?

Best Answer

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.

Related Solutions

Loading multiValued fields into Solr via flat file, and possibly value position preservation in those fields

Getting multiValued data into Solr via CSV:

The solr documentation describes a "split" function in UpdateCSV. Essentially, it parses a field value using a second CSV parser. See Solr - UpdateCSV - split. The parameters look like so (adjust field name, separator, and encapsulator as necessary):

f.fieldA.split=true&f.fieldA.separator=%2C&f.fieldA.encapsulator='

Getting multiValued data from separate fields to maintain position:

Since asking this question, I've done some reading about dimensional models. It seems that what I was trying to do is poor design, because it places too many expectations on the application, to much complexity in the warehouse, or both.

When trying to preserve the relationships between two field values on a single record, it's better to store them separately as well as together. Here's a comparison of my former input to the new input:

Former CSV input:

name|licenseState|licenseType
Josh|MA,CA|123,456
Fred|MD,OH|789,123

Transformed CSV input:

name|licenseState|licenseType|licenseStateType
Josh|MA,CA|123,456|MA123,CA456
Fred|MD,OH|789,123|MD789,OH123

This way your application can use the licenseState and licenseType dimension values independently, or it can use the licenseStateType dimension values, all without requiring complicated app or warehouse logic.

Sql-server – SSIS SQL Server 2008 errors

You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.

Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.

Fast parse settings

With only that change, the package will execute successfully

Success

Also, an excellent answer from SO on the same issue

Related Question