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

Posted on

Question :

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)?

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

Simple Workaround

You can change the flat file row delimiter to LF (from the flat file connection manager). so it will prevent package failure, and add a Script component that remove the CR value from the last column in every row

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    If Not Row.InColumn_IsNull AndAlso _
       Not String.IsnullOrEmpty(row.inColumn.Trim) Then

        Row.outColumn = Row.InColumn.TrimEnd(CChar(vbCr))


        Row.outColumn_IsNull = True        

    End If

End Sub

Also you can achieve this with a derived column transformation with a similar expression


OR (only remove last character if it is CR)

RIGHT([inColumn],1) == "r" ? LEFT([inColumn],LEN([inColumn]) - 1) : [inColumn]

Leave a Reply

Your email address will not be published. Required fields are marked *