April 22, 2014 at 3:36 am
The column delimiter for column "COL1" was not found. End Error Error: 2014-04-14 17:08:09.95 Code: 0xC0202092 Source: Load PEFC MTD Flat File Source [2] Description: An error occurred while processing file "FilePath\file1.csv" on data row 246.
I know there are many problems that happen with importing data, but this works on SQL 2005, but after migrating the package to SQL 2012 its throwing the error.
There is inconsistent data in the file, if i take the text qualifier out of the SSIS package for that column and import it, its easy to see that the entires that throw the errors have a double text qualifier around it. For example ""Data"" instead of "data".
How can this be resolved?
April 22, 2014 at 4:17 am
If I open to file up in excel, it shows the data as DATA""
Where as all of the other entries are just DATA
April 22, 2014 at 4:28 am
SQLSteve (4/22/2014)
If I open to file up in excel, it shows the data as DATA""Where as all of the other entries are just DATA
You should use a text editor rather than Excel. Looks like the value of the column is DATA".
😎
April 22, 2014 at 6:46 am
As mentioned above, in the file it is showing as ""Data"" instead of "data".
The failing data has ""DATA"" around it, where as all of the lines before are "Data" ..
April 23, 2014 at 1:39 am
SQLSteve (4/22/2014)
As mentioned above, in the file it is showing as ""Data"" instead of "data".The failing data has ""DATA"" around it, where as all of the lines before are "Data" ..
Then the actual value in that row is "Data", which in the CSV file appears as ,""Data"",
This column is being passed as Text, hence the text qualifier, what is the target/destination data type?
If the target data type is a text/string type and the TextQualifier is set to ", then this will not fail in SSIS 2012. So what is missing in this picture?
😎
April 23, 2014 at 2:14 am
Eirikur Eiriksson (4/23/2014)
SQLSteve (4/22/2014)
As mentioned above, in the file it is showing as ""Data"" instead of "data".The failing data has ""DATA"" around it, where as all of the lines before are "Data" ..
Then the actual value in that row is "Data", which in the CSV file appears as ,""Data"",
This column is being passed as Text, hence the text qualifier, what is the target/destination data type?
If the target data type is a text/string type and the TextQualifier is set to ", then this will not fail in SSIS 2012. So what is missing in this picture?
😎
Yes thats right. The column in the table is set to nvarchar(50) and in the package file connection manager it is set to Unicode string [DT_WSTR].
As mentioned, this works fine in SQL 2005 which is why I am baffled.
April 23, 2014 at 2:40 am
If you double click the Flat File Connection Manager to open the Flat File Connection Manager Editor and choose the Preview, what do you see in that column? You may have to create a test file for this with the error row closer to the top of the file.
I have tried unsuccessfully to re-create the error in 2012, my thought is that the package upgrade process has somehow gone slightly wrong.:w00t:
Probably the best way of fixing this is to delete and re-create the Flat File Connection Manager and the Flat File Source.
😎
April 23, 2014 at 6:26 am
If you go to preview you see the data as "Data" as you would expect. All of the other entires are DATA
I have tried removing the flat file source and the OLE DB Destination and adding them back in and the same failure happens..
April 24, 2014 at 7:58 am
I have put a work around in place. I have changed the text qualifier from true to false in the package and then introduced to lines of code in the following job step to -
1) change any values which should be null from "" to null
update TABLE1 set COL1=null where COL1 = '""'
2) remove " from all entries
Update TABLE1 set COL1= Replace(COL1, '"', '')
obviously not ideal, but a working solution
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply