4

I am trying to use the import and export wizard to move a small data set from a CSV file to an existing (empty) table. I did Script Table As > Create To, to get all DML for this table. I know the field type of the two fields which are causing problems is varchar(50). I'm getting this error message:

Error 0xc020902a: Data Flow Task 1: The "Source - Reconciliation_dbo_agg_boc_consolidated_csv.Outputs[Flat File Source Output].Columns["ReportScope"]" failed because truncation occurred, and the truncation row disposition on "Source - Reconciliation_dbo_agg_boc_consolidated_csv.Outputs[Flat File Source Output].Columns["ReportScope"]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)

The max length of all characters is 49, so I'm not sure why SQL Server is complaining about truncation. Is there any way to disable this error check and just force it to work? It should work as-is! Thanks everyone.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • Examine your input file. There is a least one row where len(ReportScope) > 50 characters. – user1443098 Jul 05 '18 at 20:44
  • Possible duplicate of [Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column](https://stackoverflow.com/questions/18600040/errors-in-sql-server-while-importing-csv-file-despite-varcharmax-being-used-fo) – Vinit Jul 05 '18 at 20:44

3 Answers3

1

Is there any way to disable this error check and just force it to work? It should work as-is! Thanks everyone.

Yes. If you're using the wizard, you can view the table schema before running it, and check the option to ignore truncation.

user1443098
  • 6,487
  • 5
  • 38
  • 67
  • 3
    Where is the option to ignore truncation? I didn't see it anywhere. – ASH Jul 06 '18 at 14:34
  • 3
    @Everyone Else: I opened the CSV, saved it as a text file, and imported that just fine. None of the other recommendations worked for me. The weird things is, the file came straight from SQL Server and I was trying to load it back to SQL Server (same table). It is very weird that it didn't work. – ASH Jul 06 '18 at 14:34
1

The max length of all characters is 49, so I'm not sure why SQL Server is complaining about truncation.

The default datatype of source column may be Text while using import wizard, so change it to varchar(50) using advanced tab of source. Check this link for more details.

Vinit
  • 2,540
  • 1
  • 15
  • 22
0

For the safe side can you please check column data type in both Source and Destination. If both are not same just declare all your columns as varchar inside table with some maximum length say for example varchar(max) or varchar(500) and see what would be the result.

Change max length of Varchar column:

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500);

Then the column will default to allowing nulls even if it was originally defined as NOT NULL. i.e. omitting the specification in an ALTER TABLE ... ALTER COLUMN is always treated as.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NULL;

check column nullable or Not nullable based on requirement just change it.

Use below steps for better understanding How to import a CSV file into a database using SQL Server Management Studio:

While bulk copy and other bulk import options are not available on the SQL servers, you can import a CSV formatted file into your database using SQL Server Management Studio.

First, create a table in your database into which you will import the CSV file. After the table is created:

  • Log in to your database using SQL Server Management Studio.

  • Right click the database and select Tasks -> Import Data...

  • Click the Next > button.

  • For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.

  • For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.

  • In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.

  • Check Run immediately and click the Next > button.

  • Click the Finish button to run the package.

CR241
  • 2,293
  • 1
  • 12
  • 30