title: Excel in SSIS: How to import a column that may have more than 255 characters when DT_NTEXT causes failures?
body: <p>OK, so my latest project requires loading an Excel 2007 spreadsheet into a SQL Server table. I'm working in SSIS 2008R2. Based on some stuff I found on the internet, I opened the Excel source in Advanced editor and changed the datatype of the long column to DT_NTEXT, so that it wouldn't truncate it. Then I made the database column VARCHAR(MAX). This runs correctly in debug mode on my laptop.</p>
  
  <p>Then I deployed it to the development server and attempted to load the same test file. It failed with the following error messages:</p>
  
  <pre><code>Error: Code: 0xC0208265
         Source: Main Data Flow Task Get Main Data [1]
         Description: Failed to retrieve long data for column "DESCR".
  End Error
  Error: Code: 0xC020901C
         Source: Main Data Flow Task Get Main Data [1]
         Description: There was an error with output column "DESCR" (72) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
  End Error
  Error: Code: 0xC0209029
         Source: Main Data Flow Task Get Main Data [1]
         Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "DESCR" (72)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "DESCR" (72)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
  End Error
  </code></pre>
  
  <p>Searching for information about the error, I found about a million sites offering the same three suggested solutions:</p>
  
  <ol>
  <li><p>Add 'IMEX=1' to the extended properties of the connection string.<br />
  It was already there.</p></li>
  <li><p>Change the TypeGuessRows key in the registry.<br />
  This was set to zero on the server, which I understand to mean that it should look at the entire file. Nevertheless, I changed it to 8 to match my laptop. The same error occurred when I ran it again. Then I changed it to 1,763, which is more than the number of rows in the spreadsheet. It still gave the same error. So, I put it back to zero. (There's a 1,900-character value in the first row of my test file, so it shouldn't really matter how many it checks, in this case.)</p></li>
  <li><p>Change the datatype to DT_WSTR(4000) in the source.<br />
  The column is supposed to have up to 10,000 characters, so I'm not sure this would be a good idea even if it worked. However, I tried it anyway. This time it gave me a truncation error. I changed the truncation error disposition to "ignore failure" and it loaded the data, but truncated the value to 255 characters. I have verified that the length is 4000 and doesn't get changed when I save the file, but it's still truncating at 255 characters.</p></li>
  </ol>
  
  <p>I have no idea what else to look at. Any help would be appreciated.</p>
  
  <p>UPDATE 1/29: The package, without any changes, works correctly when running on the pre-production server. It still fails when running on the development server. Both servers have the same version of SSIS (including minor version numbers) as well as the same versions of Windows, Access and Excel. I do not know how to explain this, nor do I know how to tell if it would work in production.</p>