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!

TypeConversionMode property in execute sql task in SSIS

The TypeConversionMode option is new in SQL Server 2012. This option allows the Execute SQL Task to convert data types when saving to a variable. The data types for SSIS variables do not match exactly to the data types in SQL Server. This mismatch can cause headaches due to needed data conversions. In SQL Server 2012, that headache is relieved with the new TypeConversionMode option. Set this mode to Allowed and the Execute SQL Task will convert some items to match the variable types when necessary.

   

By default value of this property is Allowed.

Let’s see the below example

Suppose we are executing our sql script task and our script return string and we want to assign this value in the package int type variable. If we are setting the property as Allowed then the package automatically converts the string data type to int.

We are taking a variable with int.

 

Running the below query

   

It will return string.

Select Result Set as Single Row.

   

This applies to the single-row result set type.

 

Now executing this task.

 

Executed successfully.

Now we are setting the property values As ‘None’.

Now running this task.

This task failed.

See the error.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "var_int": "The type of the value (String) being assigned to variable "User::var_int" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".