Dear Reader,

Importing data with SQL-Server 2005.
Using DTS in SQL-Server 2000 works fine.
Importing the data into an Excel spreadsheat works fine.

But using 2005 we get a number of issues.
So I tried to set up some tests, which resulted in 'strange' behavior.
Using the import wizard.
First 2 two line textfile imported as Flatfile with the text qualifier 
"double quote" (").
And as delimiter a comma. End of line is {CR}{LF}

The textfile:

"Just a text", "Deux"
"Second line, containing a comma", "Second field, also containing a comma."


Results in (Select * from TheTable) :

Column 0                                           Column 1
-------------------------------------------------- --------------------------------------------------
Just a text                                         "Deux"
Second line, containing a comma                     "Second field, also 
containing a comma."

(2 row(s) affected)

My first question is: Why are the double quotes included in the second 
column?
How should I interpreted this behavior?

Offcourse the import file contains more columns and more rows and has more 
variation.
But I keep getting Error on that file (See below the signature).

I do not see that the columns are to wide, or that I use the wrong character 
set.
(If I use an existing table all columns are nvarchar(250))
The import file contains 31 columns and the lines are all 401 characters 
wide.

So I was trying to go from a simple working example towards the import file 
and trying to modify (simplify) the actual import file to get to a working 
file.
(Made all columns equal length, replaced comma's in the columns with dots. 
Removed some columns, tried to import into an existing table or into a new 
table).
But am not yet succesfull.         So can anybody give some suggestions?

Thanks for your time and attention,
Ben Brugman



Our Original file resulted in:

- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data 
conversion for column "Column 19" 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 19" (86)" 
failed because truncation occurred, and the truncation row disposition on 
"output column "Column 19" (86)" 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 
"D:\DOE\BEN3.txt" on data row 1.
 (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. 
The PrimeOutput method on component "Source - BEN3_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)