Dynamic data loading of tab delimited text files using stream reader.

So I had this recent POC work , urgently needed by the client. Here is the requirement : 

1. Load input tab delimited text files. The column names might change, columns will be added , removed dynamically.
2. Some files have a weird format , but which is consistent - mostly a single line will be split into 2 lines.
3. A data warehouse to be built based on the text files.

Design approach : 

1. Use Stream Reader to load all the text files into a common staging table first.
2. Perform transformations on top of the staging table to load into the final data mart. Either use SSIS entirely or SQL code.

Output :

1. Output should hold a normalized data from these text files.

Technicalities : SSIS documentor generated - just a gist of contents : 

Contents

• ErrorDataMartLoad
• Sequence Container
• Mail - Sent Error Email Notification
• XSQL_DELETE_CURRENTDATE_DATA_IN_CASE_OF_RERUN
• XSQL-GET_ALL_FILE_NAMES
• FOR EACH SQLCARDEX ERROR FILE NAME
• FST - COPY ALL THE SQLCARDEX ERROR FILES TO DQDM SOURCE
• SCT - LOAD_STAGING
• XSQL - CUSTOMER_ERROR_DATA_ANAMOLY_UPDATE
• Sequence Container
• XSQL - LOAD_FACT
• XSQL- FETCH_ERRO_UNIQUE_COLUMNS_FOR_EACH_FILE
• FOR EACH ERROR COLUMN AND UNIQUE COLUMN IN ERROR INFORMATION 1
• XSQL - UPDATE_ERRORCOLUMNVALUE_IN_FACT
• FST - ARCHIVE_SUCCESS_FILES
• XSQL - DROP_INTERMEDIATE_ENTITIES
• SEQ - ARCHIVE_ERROR_FILES
• FST - ARCHIVE_ERROR_FILES
• XSQL - DROP_INTERMEDIATE_ENTITIES
• XSQL - DELETE_DATA_FROM_FACT_ON_ERROR
• FST - ARCHIVE_EMPTY_FILES