SSIS – Flat File Solution which have Blank records

Introduction

As we have an urgent requirement of a specified SSIS solution. The common thing that a SSIS developer always faces that the data source is not perfect.  

We have a flat file and we need to retrieve records from it. 

Looks simple but have little twist in it. The flat file has some bank records and we need to retrieve records from flat file after removing the blank records.

Case Scenario

We have a flat file named TestFile contains some blank records.

It is not only contains blank file but contains space.

Trying to retrieve records

As we understand the Case scenario, we are trying retrieve the flat file by using Redirect Row at Error output of Flat File Source Editor

When we are trying to run the Solution we got the error.

If we look at the Process Tab we find the following error generated

[Flat File Source [2]] Error: Failed to get data for the error output buffer.
[Flat File Source [2]] Error: An error occurred while processing file "E:\SSiS Pracice\Files\TestFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

So What the Solution is

As we find that, Redirection Rows is unable to provide solution in this case scenario.

The solution is quite simple and no need to Redirect Row for that.

We can get the solution by changing a property value of Flat File Connection.

In Connection manager

Flat File Connection à Properties

AlwayCheckForRowDelimiters = False (Default is True)

Hope you like it.

Posted by: MR. JOYDEEP DAS