14

I just want to import two columns from a flat file into a new table. I have set one column, 'Code', to be varchar(50), and another column, 'Description', to be nvarchar(max).

The import fails with the following messages:

- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Description" 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 1: The "output column "Description" (14)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (14)" 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 1: An error occurred while processing file "C:\Users\rinaldo.tempo\Desktop\ICD10_Edition4_CodesAndTitlesAndMetadata_GB_20120401.txt" on data row 3.
 (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - ICD10_Edition4_CodesAndTitlesAndMetadata_GB_20120401_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.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

The error message suggests to me that data is getting truncated as it's being placed in the 'Description' column, which is of type nvarchar(max)! Having eyeballed the input data, I would say the descriptions are never more than around 2 or 300 characters, so this is out of the question.

Can anyone suggest what is wrong here?

billinkc
  • 59,250
  • 9
  • 102
  • 159
David
  • 15,750
  • 22
  • 90
  • 150
  • Ah, the first task i quite clear that this is a code page conversion issue. Storing non-english hcharaccters in an ascii string? – TomTom Jan 08 '13 at 10:10
  • 3
    You have now been hazed. Welcome to the pain of SSIS. For an import this simple try `bcp`. – ErikE Jan 08 '13 at 10:11
  • I thought someone would remove my bitchy little swipe at the import wizard, but instead I've been upvoted. :D – David Jan 08 '13 at 10:29
  • Ah, there's the edit. Took longer than I thought. – David Jan 10 '13 at 13:23

4 Answers4

32

The default size for string columns, in the import, is 50 characters. This truncation happens before data goes to your database. You should adjust this in the first step of the Import Wizard, in the Columns section.

Marcel N.
  • 13,726
  • 5
  • 47
  • 72
  • Yep, for reasons I don't understand there's more than one place to set the width of the column. Thanks. – David Jan 08 '13 at 10:28
  • 1
    Thanks! I was stuck here forever thinking it was some code page issue because I did not see that I needed to set column widths in two places. – John Fischer Dec 10 '13 at 00:38
  • 2
    Yes, when you select the file to import, click the Advanced tab. Then expand the troubled field to 8000 or whatever will allow the data to come in (can't use max). Then when you get to the Select Source Tables page, click EDIT MAPPINGS and set your variable length there if you want it set to Max (or can leave as is). This is the value that will be used when the Wizard auto creates the table in your DB. – nanonerd Jun 02 '14 at 16:44
  • This is definitely the answer. It's confusing that we set the size in two places (for before the import, and then at the destination table). Surely if we specify a size in the destination table; it could "assume" we want the same size for the initial import? – Josh Harris Oct 25 '18 at 09:02
4

The error

"Text was truncated or one or more characters had no match in the target code page."

may occur EVEN when your source flat file is a Unicode file and your target column is defined as nvarchar(max).

SSIS infers data types in the source file from scanning a limited number of rows and making an educated guess. Due to endlessly repeated attempts to get it to work, it parked the metadata for the data type OutputColumnWidth to 50 characters somewhere along the way, causing truncation internal to the package.

Look into the metadata in the Data Source's "Advanced" tab to resolve the problem.

ErikE
  • 48,881
  • 23
  • 151
  • 196
Raj
  • 10,653
  • 2
  • 45
  • 52
0

you cam also try this .

select all the nvarchar columns as Dt_NTEXT in the advance TAB and then in the data conversion ,select DT_WSTR (Unicode_String) for the Alias Column for all the Nvarchar data Fields.

It worked for me :). Try it

cichy
  • 10,464
  • 4
  • 26
  • 36
user3217761
  • 1
0

I got this message as well while trying to load a 275 column table. No matter what I did, I couldn't get the message to go away. Changing one column at a time was really difficult. Fix one, get an error in another. Plus some would not seem to fix.

Then I removed all ":" and "," characters from the tab delimited source file, and it loaded just fine.

David Andrews
  • 1