SSIS Failed to retrieve long data for column varchar(max)

3
votes

I want build a package that imports excel rows to an SQL server table, the problem is that sometimes there is an error that fails the package process.

[Excel Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. [Excel Source [1]] Error: Failed to retrieve long data for column "RECOMENDACION". [Excel Source [1]] Error: There was an error with output "Excel Source Output" (9) on component "Excel Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

as you can see, there is something wrong with the column "RECOMENDACION".

Note: the error is in Excel Source task.

3
sqlsql-serverssis
The problem seems to be related to the interpretation of the column type by the ace driver. This might help you : social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Yan Brunet

3 Answers

1
votes

I had the same issue. Apparently the MS Jet Database Engine checks the first 8 rows of a column to try and guess what the data type of the column is (regardless of what type you specify).

Mine was fixed when I changed the value of the registry key TypeGuessRows from 8 to 0. This key is going to be in either of these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

HKEY_LOCAL_MACHINE -> SOFTWARE -> Wow6432Node -> Microsoft -> Jet -> 4.0 ->Engines -> Excel

0
votes

Decreasing the DefaultBufferMaxRows helped in my case.

It might be luck, but it helped.

0
votes

For me it was creating a new oledb source. I was reusing the existing oledb source for my new query. Changing it solved my issue