March 10, 2017 at 7:49 am
I'm having an issue with SSIS 2012 that I can't seem to tackle. Have a comma delimited file that is causing the package to fail with the error "column delimiter not found for column xxx". The bad data is ""SHEATH SLENDER .021"" 35MM""
I have the flat file connection set to "redirect error row" to an error table. Have Max Errors set to 100 and have FailParentOnFailure to False but the entire package is still failing instead of the row being redirected.
Can 2012 handle this or will I have to read the entire record in and parse via a script component? Surely there's a way for SSIS to handle this and I'm' just missing something.
Any help is appreciated.
Thanks
John
March 10, 2017 at 7:55 am
I'm hazarding a slight guess here, but I think the problem is the quotations. SSIS is seeing the second quotation as the end of the string, which then is not delimited.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 10, 2017 at 7:58 am
Agreed... I have the flat file source set to "redirect error rows" but it's not doing it. It's failing the package.
March 10, 2017 at 8:03 am
The problem is, it doesn't have a column to redirect the error on. It's technically erroring at the end of the column's quoted string, where it's expecting a comma. Are you able to clean your data prior to loading?
Edit, the other option would be to remove your text qualifier, and then remove any quotes at the start and end of your cell values. This, however, won't work if any of your cells have a comma (,) inside them, as it will be recognised as a new column by SSIS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 10, 2017 at 8:09 am
It's giving a specific column that doesn't have the delimiter though..... in this instance "The column delimiter for column "ITEMMASTER_DESCRIPTION" was not found".
So are you saying since it can't find the delimiter for ITEMMASTER_DESCRIPTION it's failing because it can't continue?
This data is external coming into our warehouse.
March 10, 2017 at 8:16 am
The problem is that you're saying the value of your cell is ""SHEATH SLENDER .021"" 35MM"". (Note I am assuming you have " set as your text qualifier) Thus SSIS reads the cell, and see's the first ", which is your text qualifier. It then get's to the second ", which is the second character, meaning it has got to the end of the text qualifier. This also means, to SSIS, that the string is complete, and then next value it will find will be either a column or row delimiter. instead the next character is S, so SSIS fails, as that isn't what it is expecting.
As per my edit above, you could remove your text qualifier and then strip the leading and trailing "'s in your ETL, however, if you have a cell with a value like ""LONG, TAPER .1"" 10MM"", then your going to have problems as well, as the comma in that string will be seen as a column delimiter.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 10, 2017 at 8:35 am
Understood. Makes sense what is happening.
In this situation, having the Flat File Connection setup with Error Output of "redirect row" for this column wouldn't work?? I have it setup to redirect but it's not. How does the Error Output Redirect Row work then? My assumption was that it was to handle any errors encountered in that row.
Is the only option to try and scrub the data?
March 10, 2017 at 8:54 am
The problem is that SSIS can't define the row, so it can't output error that row either.
Can I assume that your data can/does contain commas (,) within qualified strings then, as you didn't comment on that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply