SQL Server Performance Forum – Threads Archive

Error when import data from a flat file

Dear All, Urgently need a help with this, I want to import a flat file (text file) into a table. In the executing process, suddenly there is an error which told me that "the proses is failed because a truncation error", i don’t know what happen, i try to make the column width until varchar(2000), but the error is still happen, i’m very sure that the value from the flat file has not have a size as big as 2000 characters. This is the error message from the server: – Executing (Error)
Messages
* Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "so_detail" 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 "so_detail" (22)" failed because truncation occurred, and the truncation row disposition on "output column "so_detail" (22)" 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 :eek:

ffice_workunit_trans_sapcic.txt" on data row 597.
(SQL Server Import and Export Wizard) * Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source – unit_trans_sapcic_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) * 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)
Please help me guys…or refer me to another thread in this forum which has been discussed this matter before…thx in advance…

"An error occurred while processing file "D :eek:

ffice_workunit_trans_sapcic.txt" on data row 597." So what data is on that line? Look at the data that is being mapped to the "so_detail" column. You’re not giving the most relevant information, so it’s difficult to respond.

dear adriaan,
…sorry about that… the problem is: there is no something wrong with column "so_detail" at the row 597…there is no (‘) sign or anything…it’s only a text, so i don’t have a clue.. fyi, the column "so_detail" is a free text based, but i have checked in the FLAT FILE that the maximum length value for this column is not over 200 characters… btw, in the database i’ve set this column as varchar(500) and can be null. but the error is still the same…it always said the process is failed because a truncation error in this "so_detail" column, is there anyone, ever have the same problem?

quote:Originally posted by Adriaan "An error occurred while processing file "D :eek:

Dear All, I have tested another flat file which has a column that "free text" based, the same truncation error is occured, although i have set the database column with varchar(2000) Error 0xc020902a: Data Flow Task: The "output column "buy_description" (22)" failed because truncation occurred, and the truncation row disposition on "output column "buy_description" (22)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard) Please help guys…

There might be something like a CRLF halfway in a string of characters on row 597. CRLF is usually interpreted as the row delimiter, and you can get funny errors that way. Depends on the program you’re using to view the data, whether stuff like a CRLF is visible.

I’m using a tool called Ultra Edit to open the flat file, and the tool didn’t show any CRLF in the row data. Do u have any suggestion any other tool that I should use?

quote:Originally posted by Adriaan There might be something like a CRLF halfway in a string of characters on row 597. CRLF is usually interpreted as the row delimiter, and you can get funny errors that way. Depends on the program you’re using to view the data, whether stuff like a CRLF is visible.

If it’s a delimited file, import into an Excel worksheet and see exactly where it goes wrong.

hi all, The problem is with the edition of SQL server you are using.
Is it an Small Business Server with SQL 2005? .
You will need to re-install the SSIS module. AKTHAR

yes it is a delimited file with vertical bar (|) as its delimiter. The flat file can’be import into an Excel due to its row has more than 65.000 rows and Excel can’t handle that

quote:Originally posted by Adriaan If it’s a delimited file, import into an Excel worksheet and see exactly where it goes wrong.

Hi Akhtar, I’m using SQL Server 2005 standard edition… and fyi, I’m tried to import the same flat file in SQL Server 2000 DTS, and guess…it work fine!!!…it didn’t detect error in row 597 in column "so_detail" like 2005 did so, is there any bug from this SQL Server 2005 Standard Edition in the import task feature?

quote:Originally posted by Akthar hi all, The problem is with the edition of SQL server you are using.
Is it an Small Business Server with SQL 2005? .
You will need to re-install the SSIS module. AKTHAR

SQL STD should be fine. AKTHAR

if sql std should be fine, so how to reinstall the SSIS module? please advice…thx in advance

quote:Originally posted by Akthar SQL STD should be fine. AKTHAR

SQL Server 2005 Books Online
Installing SQL Server Integration Services
http://msdn2.microsoft.com/en-us/library/ms143731.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

Hi Mohammed, Thx for the link <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> btw, i want to ask u about there is any problem with import task feature in SQL Server 2005, because I’m uploading the same file with SQL 2000 DTS and working fine. And there is no problem with file too. So, maybe u know something what is going on with the import task in SQL Server 2005, is there something new in the its working way? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU SQL Server 2005 Books Online Installing SQL Server Integration Services http://msdn2.microsoft.com/en-us/library/ms143731.aspx</a> MohammedU. Moderator SQL-Server-Performance.com <hr height="1" noshade id="quote"></blockquote id="quote">

I don’t know there are any perticular issues in import process… Make sure all columns datatypes are mapped and check the row 597 to see if there any special characters…
MohammedU.
Moderator
SQL-Server-Performance.com

You can choose wither to remove and re-install SSIS:
Go to control panel – Add-remove programs
SQL 2005 – Change
Remove SSIS.
Once remove
Restart Your server
And launch SQL 2005 Setup installation and choose only SSIS OR simply re-installing by running the setup and chosing only SSIS AKTHAR

Dear All, Thx for ur replay and suggestion…i’m really appreciate it… Rgds, Adyseven

quote:Originally posted by Akthar You can choose wither to remove and re-install SSIS:
Go to control panel – Add-remove programs
SQL 2005 – Change
Remove SSIS.
Once remove
Restart Your server
And launch SQL 2005 Setup installation and choose only SSIS OR simply re-installing by running the setup and chosing only SSIS AKTHAR

]]>