HeaderRowsToSkip property in SSIS Flat file connection Manager (Skip rows when import a Flat File)

In production, we are getting the flat file to load these file into the production database. Sometimes our package get filed with error like below

[Flat File Source [2]] Error: An error occurred while skipping data rows.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202091.  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.

When we are opening the file we come to know that the reason of failure extra header come with this file.

 

Due to this, our package is failed. Generally, we are getting correct files like below.

 

Since these files come on the network folder and us don’t have access to correct theses files. In this scenario, we have two option

Ø  Either we need to send these file to respective team to correct these file and send back to us.

Ø  Or we need to make changes in our package to handle such type of scenario.

In this demo, we will see how to handle such type of scenario in our package.

Let’s start.

In the flat file connection manager, we have a property “HeaderRowsToSkip”.  This property tells the connection manager to skip n number of lines from the flat files.

If we know how many row we need to skip and it is fixed then we can set this value while we are configuring the connection.

 

Let’s run our package.

Case 1: When we have one extra header. A file like below.

 

Records in the table

 

Running this task.

Executed successfully. Basically, it skips the 1^st row and load the data in the table.

Case 2: suppose file doesn’t have extra herder

 

Records in the table

Tasks executed successfully.

 

But it loads only 2 records.

 

The reason behind it. First of all, it skips 1^st row and after that 2^nd row consider as header column. And Remaining two rows loaded into the database.

Case 3: in case one file has one header suppose file has more than one header then again our package will fail.

Package get failed

With below error.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202091.  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.

To overcome this issue we need to set “HeaderRowsToSkip” property dynamically.

Now we are taking one variable named RowsSkip as int.

By default, we are setting value as zero.

Now go to the property of the flat file connection manager.

Click on the expression.

Click ok.

Based on the source file we can set the update the variable value.

Let’s see

Case 1: Need to skip 1^st row

 

 

Setting the variable value 1

 

Now running this task.

 

The package executed successfully.

Case 2: when we are getting a good file in this case we need to set RowsSkip variable value to 0.

Setting the variable value to 0.

Running the package.

The package executed successfully.

Case 3: skip 2 rows

We need to set the variable value 2.

 

Now running this package.

The package executed successfully.

Based on the requirement we need to set the variable value. In this demo, we saw that without editing the prod file we can handle such type of scenario in our package.

Hope this demo help you to understand the HeaderRowsToSkip property in the flat file connection manager.

Keep reading!

Learn share and grow!