I'm trying to read a CSV file with this kind of lines :
"A text";"Another text";"A text with ""quotes"""
In my Flat File connection, I filled the Text qualifier as ". When I click on the Preview button, the lines are shown properly : A text with ""quotes"" (Shouldn't it show only one double quote btw ?)
But as soon as I try to execute the package, an error occurs saying that the column delimiter cannot be found:
[Source du fichier plat [1313]] Erreur*: «*Le séparateur de colonne pour la colonne «COL3» est introuvable.
If I remove those double double-quotes within the cell value it works fine.
Is there any way to make SSIS read those cells with double quotes in it ?
For the same data, you can see how 2008 versus 2012 will preview the data. Observe that Col2 either does, or does not escape the double quote (A text with "quotes" vs A text with ""quotes"")
The result of using the 2008 version is that it will fail with the following error messages
The column delimiter for column "Col2" was not found.
An error occurred while processing file "c:\ssisdata\so\input\so_36033443.txt" on data row 1.
A reproduction of the problem using Biml follows
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<FlatFileConnection
FilePath="c:\ssisdata\so\input\so_36033443.txt"
FileFormat="FFF_36033443"
Name="FFSRC" />
</Connections>
<FileFormats>
<FlatFileFormat
Name="FFF_36033443"
IsUnicode="false"
HeaderRowDelimiter=";"
CodePage="1252"
TextQualifer="""
>
<Columns>
<Column Name="Col0" DataType="AnsiString" Length="10" Delimiter=";" CodePage="1252"/>
<Column Name="Col1" DataType="AnsiString" Length="20" Delimiter=";" CodePage="1252"/>
<Column Name="Col2" DataType="AnsiString" Length="20" Delimiter="CRLF" CodePage="1252"/>
</Columns>
</FlatFileFormat>
</FileFormats>
<Packages>
<Package Name="so_36033443">
<Tasks>
<Dataflow Name="DFT Demo Delimiter">
<Transformations>
<FlatFileSource
ConnectionName="FFSRC"
Name="FFSRC so_36033443" />
<DerivedColumns Name="DER Placeholder" />
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>