MS SQL Server Import and Export Bug – When imported data is sorrunded in double-quotes

Trying to import Data into MS SQL Server using MS Management Studio v 2005, but kept erroring out with messages resembling:

Error

MS SQL Server 2005



- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 24" returned status value 4 and status text "Text 
was truncated or one or more characters had no match in the target code 
page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 24" (106)" 
failed because truncation occurred, and the truncation row disposition on "output column "Column 24" (106)" specifies failure on truncation. A 
truncation error occurred on the specified object of the specified 
component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Datafile\Sales.txt" on data row 25.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Sales.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.  There may be error messages posted before this with 
more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "SourceThread0" has exited with error code 0xC0047038.  There may be 
error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED.  Thread "WorkThread0" received a shutdown signal and is terminating. The 
user requested a shutdown, or an error in another thread is causing the 
pipeline to shutdown.  There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  
Thread "WorkThread0" has exited with error code 0xC0047039.  There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

MS SQL Server 2008



Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data 
conversion for column "Column 24" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target 
code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "Column 24" (106)" failed because truncation occurred, and the truncation row disposition on "output column "Column 24" (106)" specifies failure on truncation. A 
truncation error occurred on the specified object of the specified 
component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing 
file "C:\Datafile\Sales.txt" on data row 25.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code 
DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component 
"Source - Sales" (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.  There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)


Resolution

Weeks later, a co-worker pointed out that I needed to specify that the file I am trying to import had quoted text fields.

And, so on the “Choose a Data Source” window, specify Double-Quotes (“) in the Text Qualifier text field.

specifytextqualifier

The step taken above indicates to the import engine that your String fields are enclosed in double-quotes.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s