SQL Server Performance Forum – Threads Archive
Import CSV Truncate Error
Hi there, I’m pretty new on SQL 2005. But I need to use it to analyse some print data. All data is written to a CSV file but when I try to import this into SQL2005 using the SSIS wizard in the manager I do get the following error: (I have already tried to change the column documentname to text type did does not help.) – Executing (Error)Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "DocumentName" 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 "DocumentName" (34)" failed because truncation occurred, and the truncation row disposition on "output column "DocumentName" (34)" 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:IPCSextract WVG 30-05-2006extract-30-5-2006.csv" on data row 2.
(SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source – extract-30-5-2006_csv" (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) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
Import wizard settings:
flatfile
locale: dutch
codepage: 1252 ansi latin-1
Separtion settings are just correct..
Aren’t ssis error messages nice. Well it says that truncation is the problem on DocumentName col 34 (or the code page but I guess that’s unlikely).
I guess that doesn’t add much to what you already know.
It’s probably either a field too long or a problem with delimitters. How big is the file?
Try just importing a few lines. If that works then your setup is ok and you can try increasing the number of lines until you find the problem. You could also import the whole line into a text column (or varchar(max)) and look at it on the server.
I tend to use bulk insert rather than ssis for text files.
Thanks Nigel, On your advice I switched to use BULK Insert. Did give me some more information on the errors like column and rownr causing the error. First tried to use varchar(8000) but even this was not enough to get the import done. I now changed to varchar(max) on some of the column datatypes and bulk insert is working fine now. So thanks again. Kind Regards,
Arjan
Hi All, While I was thinking that the problem was solved with bulk insert I was thinking wrong. Alright with BULK INSERT the import went okay without errors but data was not insert correctly. I need to import about 2,5 million linesof ; seperated column’s
Text strings are qualified by double quotes " BULK INSERT ipcs_wvg.dbo.extract
FROM ‘D:IPCSextract.csv’
WITH (FORMATFILE = ‘D:ipcsqueryextract.xml’); The format file I created with BCP after SSIS already created the table when the above error occured. format file is correct. I can even insert, I don’t see errors on the documentname column like when using the SSIS wizard. The problem is that the documentname column is a text string where some lines there is also a ; in the text string. The string text qualifier is a " but I can’t figure out where to place this while using bulk insert. set SET QUOTED_IDENTIFIER ON/OFF does not help a bit. Setting the text_qualifier in the SSIS wizard still gives me the error in the first post. So BULK INSERT works but data is scrambeld because the documentname string with a ; in it is placed over two columns where it should be one. Please someone a solution? I can’t change the column seperator ; in the csv file. Thanks for the help so far. Arjan
]]>