Flat File Source Error: The column delimiter for column [ColumnName] was not found

Today when trying to import an Oracle sourced delimited file, this error cropped up:

[Flat File Source [1]] Error: The column delimiter for column “ColumnName” was not found.
[Flat File Source [1]] Error: An error occurred while processing file “MyTextFile” on data row [nnnn].
Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “Flat File Source” (1) returned error code 0xC0202092.  The component returned a failure code when etc etc…

Now this was despite the Error Output Disposition for this column being set to “Redirect Row”. So regardless of how I configured the Error Handling of the source, a single bad row would kill the load of the file.

Courtesy of a method described by jwelch at AgileBI.com I approached the issue by importing the entire contents of the file into a single column, then parsing it in a script task. So the first step was to import the entire text file into a database as a single column. This can be done by configuring the connection manager as below, setting the single column as delimited by the row delimiter:

b

Fig 1: Configuring the Flat File for a Single Column import of all data

Then I processed the data from the database table in a script task acting as a transformation on the Data Flow, with a script that read pretty much like jwelch’s. It splits the data via a delimiter using the VB Split() function, with the advantage that you can handle cases where there is an incorrect number of columns, and fixing a row in a database for reload is a lot easier than trying to find and fix a row in a massive text file.

Example script:

Public Class ScriptMain

    Inherits UserComponent
    Private columnDelimiter() As Char = CType(",", Char())
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim rowValues As String() 
        rowValues = Row.Line.Split(columnDelimiter)
        ' Test Correct number of rows
        If rowValues.GetUpperBound(0) <> 2 Then

            'Row is not complete - Handle error
            Row.Column001_IsNull = True
            Row.Column002_IsNull = True
            Row.Column003_IsNull = True

        Else

            ' Row is OK, output values
            Row.Column001 = rowValues.GetValue(0).ToString()
            Row.Column002 = rowValues.GetValue(1).ToString()
            Row.Column003 = rowValues.GetValue(2).ToString()

        End If

    End Sub
End Class

Except mine had 180 columns(!), which caused me a fair amount of mouse clicking to set up all the output columns on the Script Component. The file came through, however, successfully bypassing the flaw in the Flat File Source.

Jamie Thompson offers a slightly different approach, and there are some useful tips in the comments for that post.

About BI Monkey

Comments

6 Responses to “Flat File Source Error: The column delimiter for column [ColumnName] was not found”
  1. Mike says:

    I know large companies that will not purchase SQL Server because of this very bug. Apparently no one at Microsoft has ever worked with REAL DATA.

  2. Marcelo says:

    How to know the line number of the line in the flat file source?
    Is there any like Row.Number or using a task or somethings?
    Thanks in advance
    Marcelo

  3. BI Monkey says:

    There’s no easy way to get the row number. You can try putting a script transform after the source that counts the rows, or just looking at the row counts processed in BIDS at design time.

  4. Joe says:

    I had the same problem when reading a text file and all I had to do was to take out the text qualifier. It was initially made it to and it worked out. Might be my problem was that trivial but hope this can work out for you guys..!!!!

  5. Appuraj says:

    try using & separately

    thanks

Trackbacks

Check out what others are saying about this post...
  1. […] difficulty in handling complex or damaged text files. One approach to dealing with such files is to bring them all in as one wide text column and then split them using code. Sometimes, the file is too wide for that approach, so below is an extension of that method where […]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!