Sql-server – SSIS: Check file for row delimiter and dynamicly update row delimiter in Flat File Connection Manager at run time

sql serverssis

I am importing about 50 or so txt files into a SQL Server table. All the files have the same layout except that for some files the row delimiter used was {CR}{LF} and for others {LF} was used.

Ideally I will be asking the person who creates the txt files to stick with the same delimiter moving forward but I would like to know if there is a way that I can scan the file (maybe using a C# script) in order to capture the delimiter used in the file to then be able to update the Flat File Connection Manager's Row delimiter dynamically at run time.

Is this possible? If so, does anyone have an example of how to accomplish (or a better solution)?

Best Answer

I just found a Easy way here

You Just need to Set an Expression to Replace the '/r'(CR) in Data Flow Task

Just Awesome trick that can accept both CRLF and LF within a SSIS Package

Related Question