0

How do I import a local flat tab file into a database using SQL Server 2012 without truncation errors?

What I have tried: To import the local flat tab file into my database. I'm using Microsoft Sql Server Management System (MS SMS). I receive errors that the FULL_DESCRIPTION column is truncated. I changed the data type for that column to max and tried VARCHAR(max) & NVARCAR(max) & NTEXT(max) but I receive the same errors. I right click on the database > Tasks > Import Data.

Then the SQL server Import and Export Wizard appears.[Choose From Data Source.] > [Choose Data Destination to copy to.] > [Table Preview] > [Review Data Mapping] > Finish. It starts executing and I receive the following error screen.

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "FULL_DESCRIPTION" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."

Error 0xc020902a: Data Flow Task 1: The "Source - NameOfTabularFlatFile.Outputs[Flat File Source Output].Columns[FULL_DESCRIPTION]" failed because truncation occurred, and the truncation row disposition on "Source - NameOfTabularFlatFile.Outputs[Flat File Source Output].Columns[FULL_DESCRIPTION]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users...\Desktop\NameOfTabularFlatFile" on data row 2.

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - NameOfTabularFlatFile 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.

Here is the execution error.

What I researched. https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-flat-file-data-source-sql-server-import-and-export-wizard

sfinley
  • 41
  • 5
  • You should read [ask] and take the [tour] - you didnt actually ask a question. How to connect to a DB is infinitely and easily researched. – Ňɏssa Pøngjǣrdenlarp Jun 23 '17 at 00:49
  • I read 'How to Ask' and took the tour. I edited my question to actually be a question. I first need to make sure how I can import the flat file with the SQL server Import and Export Wizard without any errors showing truncation. Then I can see if I can automate the task with a Visual Basic Button that completes the task once clicked. -Thanks – sfinley Jun 26 '17 at 18:55

1 Answers1

0

I ended up transferring the flat file into an Excel file and importing it into the SSIS 2008 R2 database without loosing data.

I transferred the flat file into excel as Microsoft Excel 97-2003 Worksheet.

How to import an Excel file into SQL Server?

sfinley
  • 41
  • 5