SSIS–Unable to set Column Markers for Fixed Position or Ragged Right
I had a file with 72 fields to map to an SQL Server Database using SSIS. The fields position were fixed and used Flat File source for Fixed Position or Ragged Right.
After spending an hour setting the positions for all the 72 fields, I went to Advanced to define the column names
Guess what? Going back to the Columns tab, I lost almost 50% of the column markers and I have to restart redefining again? It seems a bug in SSIS as you lose your column markers or you cannot add a new column once you have moved to the advanced tab.
I found a workaround which works
1. Define the source file as delimited and name it as [Column 0]
2. Add a Derived Column and for every field defined in your file, put a substring expression. Note : Substring is one-based in SSIS
Name each field the same as in your Database to facilitate the mapping and you are done!