I receive a data feed from a third party in a pipe delimited file.  From time to time, they add a column at the end.  I would like my ssis package to continue to process the data even if they add a column with out it breaking.

How best do I handle this situation?

Eric S. Fanwick

You can import/load each row as one column then use Derived Column or script component component to map your data.

Also you can read this blog post: http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/

http://blog.concentra.co.uk/2013/06/24/ssis-2012-flat-files-now-greatly-improved-but-are-they-good-enough-

I have thought of that in the past, but not thrilled with that solution,  have any other ideas?

The last column absorbs the remaining fields until the row delimiter so if the last column in the current definition is not required, just ignore it all. If you do need the current last column, let it absorb the rest and in the derived column task, split it out and ignore the rest. (Use TOKEN if SSIS2012 up, else a substring to the delimiter - assuming TOKEN is not available pre-2012...)

• Proposed as answer by Kunal Joshi 24 minutes ago

If you have your base requirement package developed ready & working ,

its best you take last column large enough [with data type DT_WSTR probably to avoid any data sanity failure in future, as any data could come in future you wouldn't want your package fail for some special character in file in the column that you do not need at all ;) ].

Rest as Ryan already mentioned use derived column : find pipe substring , cast/ convert if you need last column data.