0

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...

This is data viewer and the file

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.

Filburt
  • 17,626
  • 12
  • 64
  • 115
Will
  • 228
  • 1
  • 2
  • 15

2 Answers2

4

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)

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Unfortunately the file has a different amount of columns each time and so I can't do this. I would have loved to be able though. – Will May 03 '22 at 16:35
  • 2
    If you truly have a varying number of columns, then SSIS the wrong tool to use. If the issue is you have say 6 different file format, then you'll want to define sufficient flat file connection managers, data flows and possibly packages to handle the known universe and then add an orchestrator to pick the appropriate package based on ... source file location, perhaps name, perhaps reading the first row and mapping row length to best candidate connection manager. – billinkc May 03 '22 at 18:16
0

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.

Will
  • 228
  • 1
  • 2
  • 15