Home Forums Data Warehousing Integration Services Load in different table base on different number of columns in text file. RE: Load in different table base on different number of columns in text file.

  • This may seem like a quite circuitous solution, but it just may work...

    This is not a step by step solution but more of an abstract...

    Loop through your files and bring the first record in - ignoring column delimiters - as a single column record and perform the following:

    Use your choice of transform (derived column, script task, etc.) to count the number of column delimiters there actually are in the record.

    So if you were using pipes as delimiters, you could count the number of pipes in the record - 1 would mean it's a 2 column, 2 means a 3 column and 3 pipes is a 4 column.

    Based on the outcome, move the original file to a directory specifically for files with that count of delimiters - i.e. 1, 2 , or 3 (representing your 2, 3, or 4 columns respectively).

    Continue looping until all files have been moved.

    This should have identified files have which number of columns and you should be able to loop through each of those sets of files and process the data as you need to for 2, 3, and 4 columns respectively.

    Hope this can help you get to your solution - if you have any questions let me know!