SSIS read flat file skip first row

Question

First of all, I did spend quite some time on research, and I know there are many related questions, though I can't find the right answer on this question.

I'm creating a SSIS package, which does the following: 1. Download and store CSV file locally, using HTTP connection. And 2. Read in CSV file and store on SQL Server.

Due to the structure of my flat file, the flat file connection keeps giving me errors, both in SSIS as in the SQL Import Wizard.

The structure of the file is:

"name of file"
"columnA","columnB"
"valueA1","valueB1"
"valueA2","valueB2"

Hence the row denominator is end of line {CR}{LF} and the column denominator is a comma{,}, with text qualifier ".

I want to import only the values, not the name of the file or the column names.

I played around with the settings and got the right preview with the following settings (see image below)

- Header rows to skip: 0
- Column names in the first data row: no
- 2 self-configured columns (string with columnWidth = 255)
- Data rows to skip: 2

When I run the SSIS Package or SQL Import Wizard I get the following 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.

I can't figure out what goes wrong and what I can do to make this import work.

Header rows to skip should be 1 if the first line doesn't contain column names - as in this case. 2 if you don't want the column names — Panagiotis Kanavos
Thanks for your quick reply. The package does run without throwing errors, but doesn't process any data. Under progress I find: "[Flat File Source [2]] Warning: The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct." — Dendrobates
Then you are probably using the wrong line delimiter - instead of CRLF use LF. Did you try previewing the data? A "Preview" button is available in the source's property page. If the settings are correct, you'll see a grid with the data properly aligned. If not, you'll see mangled data or get an error — Panagiotis Kanavos
Thanks a lot @PanagiotisKanavos, this was indeed the problem. Package runs perfectly now and I was indeed looking at the wrong place! — Dendrobates

Panagiotis Kanavos Panagiotis Kanavos · Accepted Answer · 2015-12-22T12:55:59

If you want to skip the file name and the column names, you need to set Header Rows to skip to 2. You should also check whether the file actually uses line feeds (LF) instead of CR+LF. Checking the line breaks in a text editor isn't enough to detect the difference, as most editors display correctly files with both CR+LF or LF.

You can check the results of your settings by clicking on the "Preview" button in your flat file source. If the settings are correct, you'll see a grid with your data properly aligned. If not, you'll get an error, or the data will be wrong in some way, eg a very large number of columns, column names in the first data row etc

SSIS read flat file skip first row

1 Answers