SSIS: Why won't my Text Delimiter work?

Question

Originally I had wrote the following:

Environment: - SSIS 2012, Microsoft Visual Studio

Files Involved: Destination Manager: OLEDB SQL Server Table

Source Manager: FLATFILE - CSV File

    FORMAT: Delimited
    HeadRowDelimiter: {CR}{LF}
    Column Delimiter: Comma {,}
    Text qualifier: " (manually set)
    Header rows to skip: 0
    Column Width: 100
    Column Type: DT_STR

My File has the following columns:

Year, Lg..., Div Finish, Playoffs, PF, PA...OSRS, DSRS

I wish to only retrieve:

Year, Lg..., Div Finish, Playoffs

But instead of:

|2015,'NFL',...,'2nd of 4',NULL,...,-4,0.3
|2014,'NFL',...,'3rd of 4',NULL,...,0.6,-4.4

The SSIS package would fail with a message similar to this:

[NFL_Team_List [52]] Error: Data conversion failed. The data conversion for column "Playoffs" returned status value 4 and status

text "Text was truncated or one or more characters had no match in the target code page.".

However, looking at the data that did go through a couple of times, I notice the following:

|2015,'NFL',...,'2nd of 4','339,345,-6,Quinn,Jones,Ryan...'
|2014,'NFL',...,'3rd of 4','299,422,-123,Reeves Phillips,Brooking...'

So the problem was on my delimiters are not acting like delimiters! Even though I set the Text Qualifier setting, it seems that SSIS is unable to actually enforce this rule in the dynamic FOREACHLOOP, leading to the issue.

QUESTIONS:

• Why is this the case?

• How can I get the Text Qualifier to work as designed?

Answer 1

You've set your text qualifier to " but it is actually '