I am attempting to load an excel 2007 data file into a sql 2008r2 database with ssis 2005 and the ACE 12.0 drivers. My file connection string is formatted like this: "Data Source=<file.xlsx>;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0 Xml;IMEX=1;\"" My registry settings for typeguessrows=0 My BLOB and Buffer temp storage paths have sufficient space. I tried to redirect error output for this column to a sql table to see which key it is failing on, but it errors before loading any rows. --- The weird thing is this: We have already completed data iterations and previously I had it transferring this file. With typeguessrows set to 0 it was picking up this column as DT_NTEXT. That worked just fine, loaded all 190,000 rows. When I reload the file from the last iteration it works fine again. When I load the file with the new iteration, it picks up the column as a nvarchar(255). I run it and it errors due to truncation after processing a number of rows. I change to nvarchar(4000) same results. I change again to DT_NTEXT and it wont even start processing any rows like it does for nvarchar. If you have any ideas please let me know, I greatly appreciate it!

I have isolated the issue. TypeGuessRows=0 does not scan all rows. I was unaware it was capped at 16,384 until I did more research on the setting. Failed to retrieve long data for column was thrown since it didn't find a value for that column greater than 255 chars in the first 16,384 rows of my dt_ntext column. Thanks to all who checked this out and gave it some thought!