Best in class reporting solutions from ERPs: Loading multiple flat files onto a common location using SSIS

Tuesday, 9 October 2018

Loading multiple flat files onto a common location using SSIS

Loading multiple flat files onto a common location using SSIS

Problem statement: this particular code was developed by the ETL team for the end client to resolve the following using SSIS:

· Load the transactional data from more than 80 locations onto the single staging area using SSIS

· Capture the errors during the data load. The error capture could include the following:

o File load error: the file could be absent at that point in time. In such a scenario the job is supposed to run. However the audit flag should state that the file is absent

o Data load error: this process entailed heavy data cleansing using fuzzy lookup logic.

Points to note:

· Incremental data load: the ETL team shall be using the full data extract from flat files for the given tasks. However the job ran at a frequency of 15(fifteen) minutes on a daily basis.

· Auto correct reload: the ETL team did not do auto correct reload. If a flat file was missing the job went ahead. say for example one has 60 files. the 48^th file is missing. The ETL job would not stop at this juncture. The end client was expected to furnish the required files at the given time.

Loading multiple files: for the same the ETL team designed a ‘for each loop container’ at the control flow level.

Step 2: declaration of the variable for the file path(where multiple files shall be stored).

· Set the flat file connection:

· Using the flat file manager, set up the connection string for capturing multiple flat files from the given folder. This step ensures that all the flat files in the given folder are loaded.