Today when trying to import an Oracle sourced delimited file, this error cropped up:
[Flat File Source ] Error: The column delimiter for column “ColumnName” was not found.
[Flat File Source ] 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:
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.
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
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.