Import Excel Data when column length is more than 255

When I am loading the data from excel sheet to table I am getting below error.

[Excel Source [20]] Error: There was an error with Excel Source.Outputs[Excel Source Output].Columns[Description] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". [Excel Source [20]] Error: The "Excel Source.Outputs[Excel Source Output].Columns[Description]" failed because truncation occurred, and the truncation row disposition on "Excel Source. Outputs[Excel Source Output].Columns[Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Excel Source returned error code 0xC020902A.  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.

  

My table structure as below.

CREATE TABLE product(                 productID int IDENTITY(1,1) NOT NULL,                 productName varchar(50) NULL,                 cost int NULL,                 Description nvarchar(max) NULL, )

And my excel sheet as below

First 8 product has a description of 4 characters and 9^th onwards it has more than 255.

We are getting the error in the description column. Let’s handle the error output on these columns.

Now I am running this package.

 

The package executed successfully.

See the data into the database.

 

OMG my data has been truncated and it stores only 255 characters only.

"Truncated text:  When the driver determines that an Excel column contains text data, the driver selects the data type (string ) based on the longest value that it samples (The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column). If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column. Therefore, values longer than 255 characters may be truncated." 

If we have more than 255 characters before 8 rows then it will be load successfully.

  

Now I am running this package

See in the database table.

  

In this case, it is working fine. We never know in which row we will get the more than 255 characters.

To overcome this issue we need to do the registry setting.

TypeGuessRows

 Open Registry Editor

   

Go to the below folder.

HKEY_LOCAL_MACHINE ► Software ►Wow6432Node► Microsoft ► Jet ►4.0► Engines ► Excel

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel

By default, it is 8 we need to set it 0.

Click ok.

Close it.

See the excel sheet which I am going to run.

Now running the package.

 

See the records in the table.

 

Another way to handle such types of issues using Script task or Script Component task as a source.

We will share next post on script task and Script Component task as a source.

Hope this will help for such type of issue.