Version - SSIS 2013
Client based in - USA
Our customer basically receives the zip file(s) for the health care data from various medications units and organisations (who are their clients) to be loaded to their Pre staging databases. Each zip file has one or more CSV, TXT, or any other flat file type of source with very bulky data to be loaded to Pre staging database.
We have developed a load package to accomplish this task.
The first 4 rows of each of this file contain the meta data details like the table structure, column name, data type, and size.
In some cases, only the column names are available and other details like data type and size are missing. The load package handles such cases by using default varchar(255) columns.
First they manually run the Build Pre Staging schema package that reads each of this file and creates SQL table in the specified database server, it’s a one time job.
Then, the load package runs on daily basis to load the data to these tables dynamically, it performs Upsert operation based on the given unique primary key.
Overall, it performs each following step,
It’s really a big package and many ifs and buts were elegantly handled in this package.
We also implemented parallelism into it to load the data very quickly into the Pre staging database.
© All Rights Reserved. Inkey Solutions 2018