Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Flat file quote as a firstr character suddenly a problem Expand / Collapse
Author
Message
Posted Monday, April 7, 2014 3:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 350, Visits: 1,011
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
Post #1558961
Posted Monday, April 7, 2014 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1558970
Posted Monday, April 7, 2014 3:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558971
Posted Monday, April 7, 2014 3:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 350, Visits: 1,011
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
Post #1558974
Posted Monday, April 7, 2014 4:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1558986
Posted Monday, April 7, 2014 5:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 350, Visits: 1,011
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.


Post #1558998
Posted Monday, April 7, 2014 5:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559001
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse