So apparently embedded text qualifiers are not supported in SQL Server 2005 yet when attempting to import data from a flat file.

I really hope I am wrong, but myself and another developer spent the better of an afternoon debugging this.

Heres the really simple test case which demonstrates the breakage:

Create a dummy table to import into, lets pretend it’s called “test” with two varchar(50) columns named test1 and test2.

Now create a dummy data import file (just a .txt file) with the following contents….

“this”,”is ok”

“even “”this”” works”,”12”“x18”” Print”

Now in MSQL Management Studio right click on your database name and hit import data. Set your data source to flat file, and enter your text qualifier as a double quote (“).

Hit next and you should see a warning about embedded text qualifiers… and then upon executing the procedur You will see the error:

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component “Source - test3_txt” (1) 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. (SQL Server Import and Export Wizard)

Importing the exact same file on SQL Server 2000 runs without a hitch.

Pretty lame Microsoft, you would think this would have been fixed by SP 1.