Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
are you using Data Conversion component ? –  Kin Jul 30 '13 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 '13 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. –  Kenneth Fisher Jul 30 '13 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. –  Kenneth Fisher Jul 30 '13 at 4:13

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
It helped me a Lot –  Luv Nov 7 '13 at 17:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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