Ignore Truncation Errors in SQL 2005 Import

Posted on 2009-02-19
Last Modified: 2012-05-06
How can I specify for SQL to ignore truncation errors when using the Import Wizard?  I am trying to import a CSV file into SQL using the Import Wizard.  One of the columns I have specified as nvarchar(4000).  I am getting the following error message:

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Lyrics" 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 "Lyrics" (94)" failed because truncation occurred, and the truncation row disposition on "output column "Lyrics" (94)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
Question by:thespiceman
    1 Comment
    LVL 37

    Accepted Solution

    In the "Review Data Type Mapping" step you have the possibility to change the On Truncation behaviour.  By default this is set to Fail.  Change this to Ignore and truncations will be ignored.  At least, that's what I would think but a quick test seems to indicate that this setting is not working as expected.  I still keep getting the error, even when On Error is set to Ignore as well.

    Now, what the Import Wizard does is create an SSIS (Integration Services) package that will import your file as configured through the wizard.  Here's an approach that you could try:

      o create a new SSIS project using the BIDS (Business Intelligence Development Studio)
      o right-click the SSIS Packages node in Solution Explorer and choose SSIS Import and Export Wizard.
    What you get now is similar to the wizard through SSMS.  Go through the steps and you will get a new SSIS package in the project.  You can now make the necessary modifications to fix the truncation error.  This can be done by:

      o changing the definition of the import column in the SourceConnectionFlatFile Connection Manager - Advanced tab.  Put OutputColumnWidth to 0.
      o in the Data Flow replace the Data Conversion task with a Derived Column task which adds a new colmun that truncates your column using the substring() function:
        ex: (DT_WSTR,4000)SUBSTRING(your_column,1,4000)
    Then in the Destination map your new (truncated) column to the column in the database.  This should get you to successfully import the flat file.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
    Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
    The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now