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

▲
3
▼

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.

sql
sql-server
ssis
asked on Stack Overflow Jun 21, 2013 by Diego Pacheco • edited Oct 8, 2019 by ChrisM

3 Answers

▲
1
▼

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

answered on Stack Overflow Feb 8, 2018 by lachs
▲
0
▼

Decreasing the DefaultBufferMaxRows helped in my case.

It might be luck, but it helped.

answered on Stack Overflow May 9, 2019 by Luizgrs
▲
0
▼

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

answered on Stack Overflow Jan 6, 2021 by Gamzosh

User contributions licensed under CC BY-SA 3.0