• Hi Jeff,

    A file will always have the same number of columns. The import file (a text file) will be delimited by characters and there will always be the same number of characters in a 'row'. The difficulty is that some of the fields are mutli line in the input system (eg address) and the users add carriage returns in the field (for formatting reasons).

    In this particular file, I have 53 columns and 81k rows on the import file netting down to 18k rows when I loop round and concatenate subsequent import rows to generate a single row of 53 columns.

    I have written a script task in SSIS that reads the file and then appends then next rows until I get to the right number of columns and then insert this into a table. The performance of this is acceptable - it runs in a couple of minutes. As this is a batch job that runs once a week, I am happy with the performance of it so there's no need to bust a gut on this.

    Jez