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
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
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.
