How to import Excel Data when column length is more than 255 characters in middle of a process in SSIS

Scenario:

        We have 190 rows of data in MS Excel spreadsheet. In excel data contains only 2 columns of Id and Report of Employee. The length of address is less than 50 characters but only employee id 100 contains Report length is 322.

ID	Report
1	(rpt)
2	YES OR NO
3	SYSREMOTELOGINS TYPES
4	SYSREMOTELOGINS TYPES (UPDATE)
5	AF: aggregate function
6	AP: application
7	C : check cns
8	D : default (maybe cns)
9	EN: event notification
	-
	-
	-
	-
100	select into/bulkcopy select into/bulkcopy select into/bulkcopy select into/bulkcopy select into/bulkcopy select into/bulkcopy select into/bulkcopy implicit_transactions implicit_transactions implicit_transactions implicit_transactions implicit_transactions implicit_transactions implicit_transactions implicit_transactions
101	NULL
102	sub
103	select into/bulkcopy
104	merge publish
105	ignore duplicate rows
106	Datetime12314

Solution:

 First Created a SQL table which will store the data taken from MS Excel spreadsheet.

Create table Emp_Report(

Id int not null,

Report varchar(500)

)

  I build up a SSIS Package with two connection managers and one Derived column Component. There are

    • Excel Connection Manager – This points to our MS Excel spreadsheet.

    • OLE DB Connection Manger – This points to our SQL Server database.

    • Derived Column – It converts the Unicode values into varchar string values.

 After setting up the SSIS Package, then I tried to run but it is failed due to the below reason.

Error Message: [Excel Source [65]] Error: There was an error with output column "Report" (140) on output "Excel Source Output" (73). The column status returned was: "Text was truncated or one or more characters had no match in the target code page."

If you handle the truncation error in your SSIS package it will insert only 255 characters in columns and truncate rest of the characters (Report in our case).

The reason for this is that Ms-Excel connection manager actually works on a guess. Yes, that is right it works on the assumptions. It reads a registry key called "TypeGuessRows". The default value for this registry key is 8. This mean excel will scan the first 8 row to determine the data type and data length of the column.

The valid value for the key “TypeGuessRows” can be between 0 to 8.

Run àregedit

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

       So you can change the value to 0. When the TypeGuessRows values become 0 it scans the16,384 rows before assuming the data type and data length of the column.

Finally you completes the data loading form Excel to Database. 

Let me know what you think about this article.