I recently converted an SSIS project from 2008 to 2012. This is a project that runs automatically at night, and had been for the better part of 2 years (as version 2008). Within the last few weeks, I saw some files failing, and I've had a hard time figuring out why. I got the dreaded:
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source 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.
And furthermore:
[Flat File Source [20]] Error: The column delimiter for column "NOTE_TEXT" was not found.
I dug into the data, found the line, and through trial and error verified that it was the double quote causing the issue; took it out, worked fine. Added back in, broke again.
I've gone through quite a few posts since then, and to my astonishment, apparently it SHOULDN'T have been working in 2008, and SHOULD now be working in 2012:
https://msdn.microsoft.com/en-us/library/ms140266%28v=sql.110%29.aspx
The use of a qualifier character to embed a qualifier character into a qualified string is now supported. The double instance of a text qualifier is interpreted as a literal, single instance of that string. For example, if the text qualifier is a single quote and the input data is ‘abc’, ‘def’, ‘g’hi’, the output data is abc, def, g’hi.
http://www.proactivespeaks.com/2012/06/22/my-5-favorite-sql-2012-ssis-features-so-far/
This is another painful process that has been remedied in SQL 2012. Historically, if there was a text qualifier for a flat file and that text qualifier character was in the data, SSIS would not parse it properly (Example data row: ‘123’,’456’,’78’9’). Now, SSIS can handle this scenario with ease.
I looked in the SQL warehouse for examples of notes with double quotes in them and found quite a few examples spread over the years. This definitely USED to work... all the way up to the point that the converted 2012 package took over.
I've checked package properties/variables, data flow properties/variables, flat file connection properties... they all match exactly:
Note: Even the PREVIEW properly shows the data!!!
I think for a quick fix, I'll just mark TextQualified to False for that column, then use a derived column to trim off the first and last character (the quotes), but this is ridiculous... there's got to be something I'm missing.
Any help, or any other things to check would be much appreciated.
