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 48th
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.
·
Set
up the database connection for loading the data onto the target table as
follows
Blog link: https://mndatasolutionsindia.blogspot.com/
Website: www.mnnbi.com
