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

[James Beresford]

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:



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 SubEnd ClassExcept 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.



More...