<div>
Add a Derived Column Transformation task<br />
Set up 2 variables one to store &quot;&quot;&nbsp;the other to store ''<br />
in the expression of Derived column do a REPLACE function something like this<br />
REPLACE( [yourfield], @[User::q1], @[User::q2])<br />
<br />
so as to replace 2 double quotes with 2 single quotes -
</div>


Add a Derived Column Transformation task
Set up 2 variables one to store "" the other to store ''
in the expression of Derived column do a REPLACE function something like this
REPLACE( [yourfield], @[User::q1], @[User::q2])

so as to replace 2 double quotes with 2 single quotes -

<div>
Do you do that stuff before or after (or inside?) the &quot;Flat File Source&quot; component?<br />
Thanks, but could you please provide more detail?<br />

</div>


Do you do that stuff before or after (or inside?) the "Flat File Source" component?
Thanks, but could you please provide more detail?


SSISDerivedColumn.jpg

<div>
<div class="content wysiwyg-content">
I'm trying to import a .csv file whose author chose this quoting system: two double quotes are used internally to quote an item inside a field. &nbsp;Example input file:
<blockquote>
&quot;Song Name&quot;, &quot;Composer&quot;<br />
&quot;Yellow Submarine&quot;,&quot;Beatles&quot;<br />
&quot;Symphony no 94 &quot;&quot;Surprise&quot;&quot;&quot;,&quot;Haydn&quot; &nbsp; &nbsp; &lt;--- SSIS doesn't like &quot;&quot;Surprise&quot;&quot;
</blockquote>
I have these set:<br />
&nbsp; &nbsp; Format: Delimited<br />
&nbsp; &nbsp; Text qualifier: &quot;<br />
<br />
All rows import except the lines that look &quot;&quot;like this&quot;&quot;<br />
<br />
How can I get around this problem without writing C# to manually clean up those lines before I import using SSIS?<br />
<br />
SQL Server is an enterprise-level data solution... there must be a built-in way to attack this problem.
</div>
</div>


I'm trying to import a .csv file whose author chose this quoting system: two double quotes are used internally to quote an item inside a field.  Example input file:"Song Name", "Composer"
"Yellow Submarine","Beatles"
"Symphony no 94 ""Surprise""","Haydn"     <--- SSIS doesn't like ""Surprise""
I have these set:
    Format: Delimited
    Text qualifier: "

All rows import except the lines that look ""like this""

How can I get around this problem without writing C# to manually clean up those lines before I import using SSIS?

SQL Server is an enterprise-level data solution... there must be a built-in way to attack this problem.

SSIS: Flat File Data Source: Text qualifier (&quot;) blows up for lines with internal two double quotes

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

SSIS: Flat File Data Source: Text qualifier (&quot;) blows up for lines with internal two double quotes

SSIS: Flat File Data Source: Text qualifier (") blows up for lines with internal two double quotes