Importing a large column (8000 chars) from Excel using SSIS

Question

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection to the spreadsheet. The spreadsheet is Excel 2005. The database connection manager is using Native OLE DB\SQL Native Client.

The problem I'm getting is that the OLE DB Source keeps setting a couple of the excel columns to DT_WSTR with a 255 length. I have 2 columns however that are 4000 and 8000 characters respectively. I've gone into the Advanced Editor/Input and Output Properties tab for the OLE DB Source and changed the DataType/Length in the External Columns list and the Output Columns list. Unfortunately when I leave the Advanced Editor an error appears in the error list.

Validation error. Data Flow Task: Data Flow Task: The output column "ColumnName" (226)
on the error output has properties that do not match the properties of its 
corresponding data source column.

When I click on the Source again I get an option to "fix" the errors automatically. I then select "yes" and "ok". The error is now gone but when I go back to the External Columns the datatype/length settings are back to the original DT_WSTR/255.

I could manually change the spreadsheet into a delimited file to avoid the problem but would rather not add a manual step into the process. Does anyone know of a way to get an Excel source to allow for a long column?

Answer 1

We finally resolved the issue. It turns out that SSIS calculates the length based on the first handful of rows in the excel file. When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).

Answer 2

you can split a large text to smaller chunks, in the rows and load them in foreach loop container,

Declare @maxRowLength int = 50;
with x as (
select 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s. Over the years, sometimes by accident, sometimes on purpose (injected humour and the like).' data
),
tmp(data,c,rn) as (
select data,SUBSTRING(data, 1,@maxRowLength),@maxRowLength
from x
union all
select data,SUBSTRING(data,rn,@maxRowLength),rn + @maxRowLength
from tmp
where rn < len(data)
)
select c,rn from tmp
OPTION (MAXRECURSION 500);

more information posted here