Errors in SQL Server while importing CSV file despite varchar MAX being used for each column

Question

I m trying to insert a large CSV file  several gigs  into SQL Server  but once I go through the Import Wizard and finally try to import the file I get the following error report    Executing  Error  Messages      Error 0xc02020a1  Data Flow Task 1  Data conversion failed  The data   conversion for column   Title   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  Source -   Train csv Outputs Flat File Source Output  Columns  Title    failed   because truncation occurred  and the truncation row disposition on    Source - Train csv Outputs Flat File Source Output  Columns  Title      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  Train csv  on data row 2     SQL Server Import and Export Wizard      Error 0xc0047038  Data Flow Task 1  SSIS Error Code   DTS E PRIMEOUTPUTFAILED   The PrimeOutput method on Source - Train csv   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   I created the table to insert the file into first  and I set each column to hold varchar MAX   so I don t understand how I can still have this truncation issue  What am I doing wrong

User · Answer

This answer may not apply universally  but it fixed the occurrence of this error I was encountering when importing a small text file  The flat file provider was importing based on fixed 50-character text columns in the source  which was incorrect  No amount of remapping the destination columns affected the issue    To solve the issue  in the  Choose a Data Source  for the flat-file provider  after selecting the file  a  Suggest Types    button appears beneath the input column list  After hitting this button  even if no changes were made to the enusing dialog  the Flat File provider then re-queried the source  csv file and then correctly determined the lengths of the fields in the source file    Once this was done  the import proceeded with no further issues

User · Answer

I think its a bug  please apply the workaround and then try again   http   support microsoft com kb 281517   Also  go into Advanced tab  and confirm if Target columns length is Varchar max

User · Answer

Issue  The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column   By default  the value for this key is 8  Hence  the provider scans the first 8 rows of the source data to determine the data types for the columns  If any field looks like text and the length of data is more than 255 characters  the column is typed as a memo field  So  if there is no data with a length greater than 255 characters in the first 8 rows of the source  Jet cannot accurately determine the nature of the data type   As the first 8 row length of data in the exported sheet is less than 255 its considering the source length as VARCHAR 255  and unable to read data from the column having more length   Fix  The solution is just to sort the comment column in descending order  In 2012 onwards we can update the values in Advance tab in the Import wizard

User · Answer

In SQL Server Import and Export Wizard you can adjust the source data types in the Advanced tab  these become the data types of the output if creating a new table  but otherwise are just used for handling the source data    The data types are annoyingly different than those in MS SQL  instead of VARCHAR 255  it s DT STR and the output column width can be set to 255   For VARCHAR MAX  it s DT TEXT   So  on the Data Source selection  in the Advanced tab  change the data type of any offending columns from DT STR to DT TEXT  You can select multiple columns and change them all at once

User · Answer

The Advanced Editor did not resolve my issue  instead I was forced to edit dtsx-file through notepad  or your favorite text xml editor  and manually replace values in attributes to   length  0  dataType  nText   I m using unicode   Always make a backup of the dtsx-file before you edit in text xml mode   Running SQL Server 2008 R2

User · Answer

Goto Advanced tab----  data type of column---  Here change data type from DT STR to DT TEXT and column width 255  Now you can check it will work perfectly

[sql] Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column

Examples related to sql

Examples related to sql-server

Examples related to csv