So only changing the metadata on the Excel source with the Advanced Editor did not work for me this time. I had to go full boar and ended up altering the registry to get a working solution. I changed the value of registry entry TypeGuessRows from 8 to 0. On my x64 Windows 8 machine the registry entry is located in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

The test package is attached along with the xls file. Here is the table definition too. For simplicity I only bothered to import the Asset Description column and no truncation occurred.

USE [YourDatabaseName]

GO

/****** Object: Table [dbo].[OLE DB Destination] Script Date: 5/16/2013 8:48:55 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[OLE DB Destination](

[Asset Description] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO