'SSIS - fields without the same number of characters causing space delimited file to push data to the next column
I am having a problem with a space delimited file I am uploading to SQL. This works as needed except there is a column which has occasional length differences which is separated by a space delimiter...
As you can see from the data viewer the column output from the flat file source is moving data across the columns.
I am using a space as a delimiter.
I would like to be able to have the data in the same columns even if the flight number is one less.
Solution 1:[1]
You don't have a space delimited file, you do have a fixed-width, or more likely a ragged right since it shows well in Notepad, file.
You'll need to re-define your Flat File Source accordingly. Change Format from Delimited to Ragged Right. In the Columns selector, you get to click the header bar to identify where columns are (assuming the change from delimited to ragged right drops the existing column naming and typing)
Solution 2:[2]
On this one I had to load each row as a whole rather than being able to separate into columns. I then took care of all the "heavy lifting" in SQL.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | billinkc |
| Solution 2 | Will |