I get a zip of 173 database files, in a delimited format, each day. We call this extract DbUnload. The company that sends me this file, won't adjust the file spec or give me notification of any schema changes, when they happen. I need to import this data into our own database and convert the fields into proper datatypes. This database is called Warehouse.
We had a very slow, daily build, but need to replace it, as it takes far too long and consists of 173 different script tasks (SSIS). My team and I generated some actual SSIS data flows, using C# to read the current database structure, at that time. This was faster, but stopped working every so often, as the columns would change in size, datatype, or count. We eventually decided to create some C# libraries, to simplify the parsing, importing, and file operations processes, as that is what we were using for other, simpler file extracts. This process went smooth until we had major efficiency problems with converting the data from the delimited files, into the proper data types for SQL (from .NET).
This led us to try importing the data, as raw as could be, into an instance of our table, named WarehouseRaw. We then decided to try and set up a job for converting the columns in the Raw database and inserting them into the Warehouse database, with proper data types.
- DB Extract files: DbUnload.zip (173 txt files)
- Row Delimiter: New Line
- Column Delimiter: RS special character ("\u001e")
- The file names are the table names
- First row of file is the column names
- Efficiency issues when trying to convert the datatypes before inserting into SQL
- If the column does not exist, cannot convert, or has some other issue with loading, then I would like to log an error, but load the other columns
- If the table does not exist, then I would like to log an error, but load the other tables
- Keep the raw load into WarehouseRaw. I could then generate a SQL script through C# or a tool, that I could run daily, after the initial data import.
- Find an automated tool that can be point at a source and destination Db, and then will genarate the conversion, and execute the import (truncating before).
- Find a tool to point to point to the file folder and Warehouse database and let the tool do the conversion for me.
- I have recently tried Pentaho, which did the WarehouseRaw to Raw build for one table that I configured, but I think I would have to configure this for each of the other 172 tables. I would also rather that the process be simplified and not broken out into multiple different software components/technologies.
I am just looking for the best solution that will not include me scripting out the conversion of 173 tables. I also feel like there should be an easier way to do this.