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?

  • are you using Data Conversion component ?
    – Kin Shah
    Jul 30, 2013 at 0:44
  • 1
    Does it only error on changing the length of the 8k column or does it happen with both the 8 and 4k definition? Are you open to other methods of reading Excel data with SSIS besides the Excel Connection Manager?
    – billinkc
    Jul 30, 2013 at 0:57
  • @Kin, I don't get to the data conversion component. I have a link for failed rows that sends anything over 255 characters (truncation error) off into a separate spreadsheet. I am using a data conversion component later to change from DT_WSTR to DT_STR but currently for these rows I can't get that far. Jul 30, 2013 at 4:11
  • @billinkc The error happens when I change the value to anything other than the original 255 characters. I am more than happy to use something other than an Excel Connection manager. My only requirement at this point is that it is an automated process that I can explain to my developers so they can support it. Jul 30, 2013 at 4:13

2 Answers 2


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


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

more information posted here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.