SQL Server Performance

Error when import data from a flat file

Discussion in 'SQL Server 2005 General DBA Questions' started by adyseven, Mar 11, 2007.

  1. adyseven New Member

    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...


  2. Adriaan New Member

    "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.
  3. adyseven New Member

    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: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.
  4. adyseven New Member

    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...
  5. Adriaan New Member

    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.
  6. adyseven New Member

    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.
  7. Adriaan New Member

    If it's a delimited file, import into an Excel worksheet and see exactly where it goes wrong.
  8. Akthar New Member

    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
  9. adyseven New Member

    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.
  10. adyseven New Member

    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
  11. Akthar New Member

    SQL STD should be fine.

    AKTHAR
  12. adyseven New Member

    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
  13. MohammedU New Member

  14. adyseven New Member

    Hi Mohammed,<br /><br />Thx for the link <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />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?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MohammedU</i><br /><br />SQL Server 2005 Books Online <br />Installing SQL Server Integration Services <br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms143731.aspx>http://msdn2.microsoft.com/en-us/library/ms143731.aspx</a><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  15. MohammedU New Member

    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
  16. Akthar New Member

    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
  17. adyseven New Member

    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

Share This Page