I assume you're trying to import this using an Excel Source in the SSIS dialog?
If so, the problem is probably that SSIS samples some number of rows at the beginning of your spreadsheet when it creates the Excel source. If on the [ShortDescription] column it doesn't notice anything too large, it will default to a 255 character text column.
So to import data from a column that contains rows with large amounts of data without truncation, there are two options:
- You must make sure that the [ShortDescription] column in at least one of the sampled rows contains a value longer than 255 characters. One way of doing this is using the REPT() function, e.g. =REPT('z', 4000), which will create a string of 4000 of the letter 'z'.
- You must increase the number of rows sampled by the Jet Excel driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel(of if your system is x64 then under the
HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Jet\4.0\Engines\Excel) registry key.
You can see more information at these two links:
To further explain, SSIS creates 3 objects behind the scenes of the wizard, an Excel data source object, a SQL table destination object, and a data flow operator between them. The Excel source object defines the source data and exists independent of the other two objects. So when it's created, the sampling I described is done and the source column size is set. So by the time the data flow operator executes and tries to pull the data from excel to put in your table, it's already looking at a data source that's been limited to 255 characters.