February 14, 2008 at 8:48 am
Hi,
I have realized a data flow task that reads from an excelsheet, one Input columns "Description" is defined as DT_NTEXT, since it contains strings longer than 255 characters.
No I tried the task on a different workbook (same sheets and same columns of course but different data). But the data source (ValidateExternalMetadata=False) returns an Error "Failed to retrieve long data for column Description". Taking a closer look the main difference between both sheets is that the second sheet contains strings longer 255 characters only beginning with the 100th row whereas the original sheet had such long strings already in the first row.
When I modified the data in the second sheet row number 5 now containing a 300 character long string it worked fine.
I don't understand why SSIS has problems reading short strings in a DT_NTEXT. But anyway: Do you know a solution making SSIS accept that data no matter whether the actual data is short?
April 25, 2012 at 6:49 am
Hi Any one can help on this issue?..............
April 25, 2012 at 7:51 am
could you please upload the sample excel sheet you are trying to upload?
I had a similar issue but with dt_wstr datatype, I tried converting the datatype to dt_str using derived columns transformation within my dataflow and it worked. Not sure if you are facing the same problem.
April 25, 2012 at 8:00 am
i had this problem before - have a look in the registry under the Jet 4.0 engine settings and there is a registry value that is used to "automatically determine" the data type of an excel column (regardless of what you specify) - it's set to something like 30 rows of data in the excel column.
try modifying your sheet and forcing one of the first rows to be more than 255 chars - if the porblem does away then you need to modify the reg key.
there are other workarounds, but it's been several years since i hit the problem.
it's available on google and if i find it i'll post it here
MVDBA
April 25, 2012 at 8:03 am
i just found a whole bunch of other sites where this is a known issue by googling Jet 4.0 first rows
the actual number of sample rows is 8
more info as a starting point here (including the reg key)
MVDBA
April 27, 2012 at 2:52 pm
hi i am facing a similar issue, I have an excel connection manager. The excel file to which the connection manager is pointing has a column with 257 characters (row number 176). Some how the metadata for this column is set to dt_wstr (255). I have changed it to dt_Wstr (500) from the advanced properties of the connection manager, also in the registry i have changed the value of typeguessrows in the registry to 16 but still i keep on getting the following error -
There was an error with output column "DQ2Other" (10457) on output "Excel Source Output" (10312). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
I really cant move ahead with this, any help would be greatly appreciated.
April 27, 2012 at 2:57 pm
Please ignore my previous post, I set the value of typeguessrows to 1000 and it works fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply