Greetings to all experts in this forum, this is my first post.

Recently, I was given similar task except that we do have the field names and data types on a separate CSV file. That is the good news. The bad news is, there are hundreds of CSV files to be loaded into hundreds of tables.

Not so keen to create hundreds of SSIS packages by hand, I remember one blog here about generating SSIS packages dynamically using BIML. First, I create a Metadata table and populate it with Filename, ColName, DataType, etc. Then run a BIMLscript which reads the Metadata table and generate the SSIS packages in about 5 minutes. (Pause for applause :cool:)

I also have another BIMLscript that reads the Metadata table and generates the SQL (DDL) for creating all the tables. This certainly helpful first time. But later I realised it is even more useful because the Field specifications we got in that CSV file was either inaccurate or out of date. I just update the Metadata table. Then re-run the BIMLscript to generate both SSIS package and DDL only for the one needs to be fixed. The whole thing was done in two days.

Even more benefits when the client later told us that they need some kind of auditing (Row counts for Source, Loaded, Rejected). So now, all I need to do is change the BIMLscript and re-run to generate all the hundreds SSIS packages with auditing added.

Regards,

Albert B.