SSIS mysterious error

Question

I got only these 2 errors when converting flat files to database tables. The dataflow seems to work for other flat files with the same layout so I don't understand where these errors could come from.

[Flat File Source [204]] 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.

What type of files (csv, txt, bin)? What is the delimiter? SSIS should be able to tell you what row it choked on if you watch the OUTPUT window during debugging. — Vinnie, Apr 23 '14 at 02:09
txt files, tab delimited, row delimited {CR}{LF}, I am looking at the output/progress windows and that gives me only these 2 errors but maybe you mean another window?? Also the error output on all components return nothing :S — BigChief, Apr 23 '14 at 02:14
Are you skipping a header row or any number of other rows at the start of the file? — Vinnie, Apr 23 '14 at 02:15
skip is set to 0 but I checked "Column names in first data row" — BigChief, Apr 23 '14 at 02:17
I would try to capture the row that it is failing on. The error would indicate to me that there is a bad, missing or additional delimiter in the file and it's violating the format. The other possibilities are not visible characters (unicode sometimes does this) or bad row terminator. — Vinnie, Apr 23 '14 at 02:24
Wow that sounds bad since it does not return a row number or error output. Any idea how to find out where the error occurs? — BigChief, Apr 23 '14 at 02:28
There ought to be more error messages in the output pane. Could you double check that you haven't inadvertently missed one of them? — billinkc, Apr 23 '14 at 03:28
In the progress pane only these 2 errors are visible!! :S But a bit further a warning is visible: Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. I already tried to change maximumerrorcount but no luck. — BigChief, Apr 23 '14 at 20:09
Keep looking.... there should be an error message that is more helpful somewhere. — Nick.Mc, Apr 24 '14 at 11:57
Nothing else. It threw up an error because of a missing column in the tab delimited text header, which it was expecting in newer files... — BigChief, Apr 26 '14 at 00:22
Is the file small enough to manually inspect? Sometimes, you can see an odd character or a long string of spaces in the place of a delimiter. — Rhys, Apr 23 '14 at 13:56
It's only editable using gVim, it is too big for most other text editors (2,3GB)... In the flat file editor preview the file is viewable and the colums/rows are properly visible. In excel however, when doing txt import, nothing is visible in preview. Probably because it is too big but I'll try checking it with hex editor now for wrong characters. — BigChief, Apr 23 '14 at 20:07

BigChief · Accepted Answer · 2016-03-19T16:39:32.693

24

Ah i found it... files were older and had some columns missing in the header..........

edited Mar 19 '16 at 16:39

answered Apr 24 '14 at 10:42

BigChief

1,413
4
24
37

1

Thanks! this solved my problem! The error message is rather misleading. – remondo Dec 27 '21 at 15:39

score 13 · Answer 2 · answered Nov 10 '15 at 13:37

I agree with the answer above "files were older and had some columns missing in the header" (and I upvoted this answer). I just had the same error, and after investigation it was because the external flat file had a column missing, compared to the columns expected by the flat file connection manager. In essence, the SSIS error "An error occurred while skipping data rows" should be interpreted as meaning "Check the actual columns in the flat file source match the columns defined in its connection manager".

score 5 · Answer 3 · answered Jul 13 '16 at 17:32

5

You might need to check if all the required columns are available in flat source file.
check in Flat File Connection Manager for "number of header rows to skip" and confirm it to be correct based on the source file content.

answered Jul 13 '16 at 17:32

user6585779

51
1
1

score 3 · Answer 4 · answered Jun 01 '15 at 20:32

3

I was able to resolve it by setting header rows skip count to zero (0).

Flat File Connection Manager --> Header rows to skip: 0

answered Jun 01 '15 at 20:32

khichar.anil

4,585
1
23
21

score 2 · Answer 5 · edited Feb 01 '17 at 10:21

2

Having had the same error message, I managed to correct the problem. My analysis has gone up 2 problems:

first problem

the HEADER ROWS option allows to manage the headers of lines marking the structures. If you set for example 1, it will discard the first column of the line. The returned error comes from desynchronizing output columns - you have more columns output than those from the file because you have removed the first column. Reset HEADER ROWS to 0 and set Data rows skip to 1 to remove the header if leaving 0

Second problem

if you use the column names option in the first row, it allows you to quickly create the columns, but it raises problems when executing the data flows. Stay on using the data rows skip a 1 and rename with the advanced option the columns and their type

edited Feb 01 '17 at 10:21

Alexei - check Codidact

22,016
16
145
164

answered Feb 01 '17 at 09:58

thewolf62

21
1

1

i did what you suggested but no fix. i am running SSIS through VS 2019 on SQL Server 2019 (basically all the latest updates as of date of writing). bottom line is: if you need to create 1 new column at the end of all the current column sequence, then SSIS doesn't know how to handle the skip row settings. it's basically a pure unadulterated mess a la Microsoft! i happen to have more than 250 columns to deal with over 1,000s of files. so no way i can edit anything by hand. need to figure out how to reinject the column header names. nightmarish! oh wait! my fix suddenly stopped working! – pdp Oct 25 '20 at 21:16
1

SSIS is truly the biggest piece of garbage ever to come out of Redmond. – pdp Oct 25 '20 at 21:17

score 2 · Answer 6 · answered Jan 31 '18 at 11:43

2

I encouter the same error message in a SSIS project. The problem was in a connection of flat file source. Just recreating the connection worked for me.

answered Jan 31 '18 at 11:43

Drift_Maniac

23
4

score 1 · Answer 7 · answered Jan 30 '15 at 05:23

For the benefit of others with this error, I had the same issue (An error occurred while skipping data rows) but it was not due to quote marks in the file, delimiters, or qualifiers. I am not skipping any header or data rows either. So it's a pretty annoying error message, which gives no insight whatsoever into the actual problem.

Anyway I was able to get it working by checking 'Retain null values from the source as null values in the data flow' in the connection manager tab of the flat file source editor.

score 1 · Answer 8 · answered Dec 14 '20 at 21:33

I had one blank row following my header row. I had said skip one data row, but that apparently just meant while previewing data. I created the data source with the header row, and then, once the data source was created, I said skip the first 2 rows of the file (Header skip 2). That solved the problem.

score 0 · Answer 9 · answered Nov 03 '15 at 18:11

0

I had this problem when transferring tables which contained a SQL Geometry column.

answered Nov 03 '15 at 18:11

James

19

SSIS mysterious error

9 Answers9

Linked