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
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
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?