SSIS Script Component - Parsing Text Files Dynamically
Essentially, I am trying to read an incoming text file(s) and these files are going through a ForEach Loop and assigning variable for the file name that is being used in the data flow section for the Flat File Source object.
Then I use a Script Component object (C#) set as destination type. I am in need of code that can read a text file and dynamically insert into different tables based on the column values received for a particular data set. The challenge is that the data sets are not standardize contained in a single text file. One data set can have 10 columns and the next data set can have 20 columns. A space is the indicator that there is a new data set inside of the text file (which could have 500 - 1000 data sets per text file). I am able to create set values (using Script Component type of Transformation) and push out to SQL db with no problem but issue is sometimes there are 1000 columns and I do not want to hard code that many columns. The other issue is that the columns are not always in the same sequence and when you have it as set values they have to be in the same sequence as the text file or it will skip the output value. does anyone have any examples I can leverage in C#? Thanks.....