June 22, 2010 at 2:34 pm
I have seen a few posts on the internet about this topic, but they don’t seem to be hitting the mark. So I will create another one that hopefully will. The problem I have is I have a text file that is being produced by a source I cannot control. The file is a comma delimited file. The first row has the column names. The problem is the source will sometimes produce the file with the column in a different order than since the last time (first time ColA, ColB, ColC, next time ColB, ColA, ColC). If you have ever imported a file before, you know that the columns and the order they are in are stored a design time (why I will never understand). Therefore, if the columns are switched at run time, then you have data going into the wrong column or worse yet you error out. You can always go back into the connection manager for the text file and Update the columns, but that doesn’t help during run time.
So, does anyone have a good way of dealing with this issue?
June 23, 2010 at 1:33 am
You could store the file contents into a new staging table with char columns and try to parse the contents of each column to identify the order they were written in the file.
You could do the same thing in code parsing the first row of the file and change, let's say, the format file for a bulk insert. I'm no SSIS expert and I don't know if this can be done changing some properties, but you could give it a try.
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply