Importing flat files in SSIS with multiple format files

Question

I'm currently trying to create a SSIS package that would import a flat file into a SQL Database table.

There are about 30 different flat files each with it's own format file that I have to make a table that contains all relevant data. The flat files first line does not contain column headers. I.E. one file could have PostalAddress1 and another PostalAddressLine1 and those would be 2 columns in the database and they'd be null-able. Some flat files might contain more data in which case those with less will have a null value.

As i'm rather inexperienced with SSIS, I thought I'd use a script to parse the flat file to get the row and column delimiters as well as all the column names. I'd then set the flat file source (variable) and set it to only have a column and each record being a row in the text file, afterwards use a Script Transformation to parse each row and set it to the relevant column. I.E. value 5 is FirstName I'd set it to Row.FirstName = Value[4];

After the entire file is processed a sql task can run that can then push all the data from the generic staging table into another table that would say only have only PostalAddress field instead of say 3 to accommodate for each format file supplied.

Some of these files could be up to 2.5gb in size.

My questions are:

Would this be the correct manner to do this?
Is there a faster/better/simpler way to do it?

Answer 1

This is a good candidate for BiML, which dynamically builds packages based on meta data.

From the BiMLScript introduction:

Even better, automation logic can be embedded directly into your Biml code using BimlScript code nuggets. These snippets of C# or VB code enable you to access external metadata to automatically build large amounts of Biml code. For example, you can automatically create staging environments or apply SCD patterns based on column metadata.