Ignore Truncation Errors in SQL 2005 Import

Posted on 2009-02-19
Medium Priority
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

ValentinoV earned 2000 total points
ID: 23692237
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

862 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