Flat file with a standard of 4

Hi

I am trying to import a flat file into a table, and from there select values from the table and insert the appropriate values into different tables

The flat file is pipe delimited. I.E

File Example:

01|Name|Surname|BenCode|Counter||||||DateTime

02|Name|Surname|BenCode|SchemeID|SchemeName|

03|Name|Surname|BenCode|ID||||Date_From|Date_To||||||||||

04|Name|Surname|BenCode|SchemeID|SchemeName||||CodeID|CodeDescription||

All these different fields are in one flat file. (It would be nice if they were in 4 seperate flat files but they're not)

I want to take the file, where the ID = 01 then the data must go into table Q1

WHERE the ID = 02 then the data must go into table Q2 and so on

When i tried to do it with SSIS, it started creating columns according to the file, but it takes the first row and counts only that rows fields and calculates the columns based on the firs record, but some of the records have more fields than that of the first row.

If i can just get this flat file imported into a single table then i can split the data up based on the table.

Any ideas will be welcome. I'm quite new to SSIS.

Kind Regards

Carel Greaves

To handle the varying number of columns, you can bring each row in as a single column, then parse it in a script component. By adding multiple outputs to the script task, you can send each record type to it's own unique output. Here's a few examples:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx