Ssis Reading Lf As Terminator When Its Set As Crlf

July 08, 2023 Post a Comment

using SSIS 2012. My flat file connection manager I have a delimited file where the row delimiter is set to CRLF, but when it processes the file, I have a text column that has an LF

Solution 1:

I have no SSIS experience but as an ETL developer I have faced this many times. So my suggestions might not help you solve the problem but hopefully point you in the right direction

If the problem field has text qualifier (single or double quote usually) and SSIS supports use it
Also if there is an option to force SSIS to use different end of record delimiter other than LF (CRLF in this case) I'd use it (hopefully there is no CRLF in the problem field text)
If the problem field is not the last field, you can count the number of de-limiters by reading the entire record as a single LF delimited field to identify and filter out the problem records (if they are only few) and try to stitch them back
If possible read the file as single record (if SSIS has an option) and replace all LF, provided CR is consistent end of record delimiter from the source

Solution 2:

Before answering, i don't think that the column contains only LF because if the row delimiter is CRLF it will not consider it as delimiter. So it is probably CRLF, but i will give a solution for the two cases (CRLF or LF)

Solution

You can fix this situation with the following steps:

First in the Flat File connection manager add only one column (of type DT_STR and length 4000) so you will consider each row as one column.
In the data flow task you have to add a Script component that fix the file structure. and split row into columns.

Simple Test

I will consider a flat file with the following content

ID;name;DOB;Notes;ClassID{CRLF}
1;John;2001-01-01;;1{CRLF}
2;Moh;2002-01-01;Very cool{LF}
Genius;2{CRLF}
3;Ali;2000-01-01;Calm;2{CRLF}

First i will add a flat file connection manager with the following options:
- Row Delimiter = {CRLF}
- Header Row Delimiter = {CRLF}

In the DataFlow Task i will add a Flat File Source, 2 x Script Component , OLEDB Destination
In the first Script Component i will mark Column0 as input and i will add 5 output Columns ID,Name,DOB,Notes,ClassID and i will set the Output Synchronous Input as None

In the first Script Component i will write a script that store each line in a memory variable and assign it to an output row when row is complete and another row is present.

Dim strLine AsString = String.Empty

Dim strDelimiter AsString = ";"PublicSub EmptyMemoryVariables()


    strLine = String.Empty


EndSubPublicSub AssignMemoryVariablesToOutput()

    With Output0Buffer

        .AddRow()
        .NewRow = strLine
    EndWithEndSubPublicFunction AreVariablesEmpty() AsBooleanIf strLine = ""ThenReturnTrueElseReturnFalseEndIfEndFunctionPublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim strColumns AsString() = Row.Column0.Split(CChar(strDelimiter))

    If strColumns.Length = 5ThenIfNot AreVariablesEmpty() Then
            AssignMemoryVariablesToOutput()
            EmptyMemoryVariables()
        EndIf

        strLine = Row.Column0

        AssignMemoryVariablesToOutput()
        EmptyMemoryVariables()


    ElseIf strLine.Split(CChar(strDelimiter)).Length = 5Then

            AssignMemoryVariablesToOutput()
            EmptyMemoryVariables()

        EndIf


        strLine &= Row.Column0







    EndIf

In the second Script COmponent i will split each row into Columns

Dim strDelimiter AsString = ";"PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim strColumns AsString() = Row.NewRow.Split(CChar(strDelimiter))


        Row.ID = strColumns(0)
        Row.NAME = strColumns(1)
        Row.DOB = strColumns(2)
        Row.NOTES = strColumns(3)
        Row.CLASSID = strColumns(4)


    EndSub

Important Note: the provided code is not optimal it may need more validations or can be simpler and better but i am trying to give you the way you can think to solve this issue

Solution 3:

thank u for all the suggestions. turned out that the vendor had changed the encoding of the file from Ascii to unicode. changing the the package to read the correct encoding did the trick.

Solution 4:

In your Flat File Connection Manager component you have a property that I forgot its name, in it you can set the row delimiter ({CR}{LF}, {LF}, {CR}, ...etc).

Please try to adjust this property I think it'll work.

Solution 5:

I had a similar issue to this. I had a CSV file with LF as the terminator. However, the client also had CRLF in two of the columns and this was causing the "delimiter for column is not found" error.

It took me a few days of googling solutions and trial and error, but I got it working.

In the end, I needed two script components.

In the first Script component, I had a column named Output0 string with Length of 4000. In the script (see below) I used ReadToEnd to load the data, replace the CRLF with an empty string, and then spliting into rows with the LF as the terminator.

using System.IO;
using System.Text;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
publicclassScriptMain : UserComponent
{
    private StreamReader textReader;
    privatestring collateralFile;

publicoverridevoidAcquireConnections(object Transaction)
{

    IDTSConnectionManager100 connMgr = this.Connections.Collateral;
    collateralFile = (string)connMgr.AcquireConnection(null);

}

publicoverridevoidPreExecute()
{
    base.PreExecute();


}

publicoverridevoidCreateNewOutputRows()
{

    StreamReader textReader = new StreamReader(collateralFile);
    string collatFile = textReader.ReadToEnd();


    collatFile = collatFile.Replace("\r\n", " ");
    
    String[] lines = collatFile.Split(newchar[] { '\n' });
    textReader.Close();


        string nextLine;



        for (int i = 0; i < lines.Length; i++)
        {
            if (lines[i] != null)
            {
                nextLine = lines[i];

                if (!String.IsNullOrEmpty(nextLine))
                {
                    Output0Buffer.AddRow();
                    Output0Buffer.Output0 = nextLine;

                }
            }
          }

        }
    }

I tried splitting it again into columns, but it returned null values, so in the second script component I created my columns and loaded the data into them in the script.

publicoverridevoidInput0_ProcessInputRow(Input0Buffer Row){

String[] columns = Row.Output0.Split(',');

Row.Description = columns[0];
Row.LegalDescription = columns[1];
Row.Address1ParsedLine1 = columns[2];
Row.Address1ParsedLine2 = columns[4];
Row.Address1ParsedCityname = columns[5];
Row.Address1ParsedStatecode = columns[6];
Row.Address1ParsedPostalcode = columns[7];
}

Lab Sql