April 7, 2014 at 3:32 am
Ok I got the following issue:
SSIS to import flat files,everything went fine for months,now suddenly errors.
At first the errors indicated an error in the file (the provider of the file has the habit of making undocumented & un-announced changes to the files)
But this time it wasn't it,so I checked the file and see the following situation,one of the fields start with a double quote (most likely to escape a quote)
For example: "RAC ('RAC)
The flat file has a text qualifier of ''.
So all of this worked fine,this scenario has happened before with no issue,suddenly now its an issue.
Sorry for brief,undetailed & maybe chaotic explanation,but let say I'm not really understanding why this is occuring
April 7, 2014 at 3:50 am
Resender (4/7/2014)
Ok I got the following issue:SSIS to import flat files,everything went fine for months,now suddenly errors.
At first the errors indicated an error in the file (the provider of the file has the habit of making undocumented & un-announced changes to the files)
But this time it wasn't it,so I checked the file and see the following situation,one of the fields start with a double quote (most likely to escape a quote)
For example: "RAC ('RAC)
The flat file has a text qualifier of ''.
So all of this worked fine,this scenario has happened before with no issue,suddenly now its an issue.
Sorry for brief,undetailed & maybe chaotic explanation,but let say I'm not really understanding why this is occuring
Starts with a double quote but does not end with one? And your text qualifier is "? I would expect an error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2014 at 3:50 am
A double quote is not the same as two single quotes.
A double quote is one character, so I don't think it was there to escape a single quote.
The error probably comes from the fact you have an opening double quote but no closing double quote, since the double quote is the text delimiter.
This is an error in the file and you should contact the provider.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 3:56 am
ok 2 great answers
so the line I'm getting the error on is getting is something like this
''Rac ..... ''Rac company description ..... 251
Now what happens the next line instead of being the next line is attached to this one so that's what cause the error.
Now I'm wondering what do I need to change to keep ''Rac or 'Rac in the fields
April 7, 2014 at 4:22 am
Resender (4/7/2014)
so the line I'm getting the error on is getting is something like this''Rac ..... ''Rac company description ..... 251
Now what happens the next line instead of being the next line is attached to this one so that's what cause the error.
So you're missing a row delimiter on that line?
edit: what is the actual error that you get?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2014 at 5:05 am
Ok here's an example of the error I'm getting for the file,note that I post edited the names,now we see here is a truncation error now what's actually happening here is the following.
The flat file is a csv with a a text delimiter '' (2 single quotes),header row delimiter of {CR}{LF}, 0 header rows to be skipped
A row delimiter of {CR}{LF} and a column delimited of tab (I would never use tabs since I'm seeing to much errors where space & tab are misidentified)
Now I got a company name field,followed by 5 a 6 columns with various info's and then a column with a description,usually holding the company name as well.
So what happens here is that all the columns between company name (& company name gets included) to the column just before the description are all put into the column company name in that row,this cause no truncation on the company name since it still fall between the max length.
However what happens then is that it can't correctly see the next row and so its adding everything of the next row in field Column 50 & this causes the truncation error.
But when I add a char before the '' everything is fine,however that's not what is supposed to happen.
SSIS package "X_X_X_item.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\Users\Resender\Documents\X Test\X_item-04-02-2014.csv" has started.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 50" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "Column 50" (1406)" failed because truncation occurred, and the truncation row disposition on "output column "Column 50" (1406)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\Users\Resender\Documents\X Test\X_item-04-02-2014.csv" on data row 2.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. 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.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\Users\Resender\Documents\X Test\X_item-04-02-2014.csv" has ended.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [530]: The final commit for the data insertion in "component "OLE DB Destination" (530)" has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [530]: The final commit for the data insertion in "component "OLE DB Destination" (530)" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (530)" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Error: 0xC002F304 at Failed, File System Task: An error occurred with the following error message: "The file exists.
".
Warning: 0x80019002 at X_X_X_item: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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.
SSIS package "X_X_X_item.dtsx" finished: Failure.
April 7, 2014 at 5:12 am
Right.
What happens is that you have your two single quotes opening as text delimiter.
SSIS will continue reading data and adding it to the column until it encounters the two closing single quotes. It ignores everything that it comes accross - column delimiters, row delimiters and so on - until it encounters those other two single quotes.
Since your column mistakenly doesn't have those, SSIS goes on until the next row, where it finds two other single quotes. It thinks the column ends there. Result: your column is way too big because it included data from other columns.
Solution: choose a decent text delimiter or just don't use one at all but choose a decent column delimiter.
If neither is an option, you need to make sure that if a column opens with a text delimiter, it is also closed by a text delimiter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply