For the life of me, I cannot seem to get past the following error:
Error: 0xC020901C at Import Data - APA, APA Workbook [2]: There was an error with APA Workbook.Outputs[Excel Source Output].Columns[Just] on APA Workbook.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Import Data - APA, APA Workbook [2]: The "APAC Workbook.Outputs[Excel Source Output].Columns[Just]" failed because truncation occurred, and the truncation row disposition on "APA Workbook.Outputs[Excel Source Output].Columns[Just]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component."
I have an SSIS package that is trying to load data from an Excel file into a SQL Server table. I understand SSIS takes a "Snapshot" of the data and uses this to build the column sizes. My database column for this column is: nvarchar(512).
So some things I have done to try and rectify this are as follows:
After I did the above, I ran the code and the 10 rows of data were imported with no issue. But when I run it against the real Excel file, the error appears again. But I notice that if I sort the Excel file by the LEN() of the column, putting the largest at the top, it works. But I cannot ask the users to do this who send the file, but odd it doesn't work as sent but if I sort it, it works
I would appreciate any help on how to solve this, as all of my Google searches state the above would work, but unfortunately it is not.
May 26, 2021 at 2:12 pm
It's been a while since I last worked on this issue, so I may have misremembered the 'solution'.
After your package has run against the dummy Excel file with the wide columns, try setting the 'ValidateExternalMetadata' property of your Excel OLEDB connection to false and save the package.
Then try executing it again against the real data file.
Good luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2021 at 5:06 pm
I appreciate the response, but unfortunately, this didn't work. The first file, with the "dummy" data loaded great. I then changed the setting on the Excel Source step to "False" for ValidateExternalMetadata, and ran the code again, and got the truncation error.
May 26, 2021 at 5:48 pm
The error is either truncation or a code page issue - are you sure there are no characters in the source Excel file that could be causing issues?
What happens if you define that column in the database and the SSIS package as nvarchar(1024)?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2021 at 6:37 pm
As far as I could tell, I didn't see or notice any bad characters in the records that wouldn't import. I also have the columns set to NVARCHAR(4000) just to test, but that isn't helping either.
What is really confusing about it all, is if I take the Excel file, and sort the columns by the one with the largest character count, the data will import. So this is another confirmation to me that the data doesn't have any bad characters. If I take the file as sent by the user, I get truncation errors, but if I take the same file, sort by the column giving me an issue by LEN() and then put the largest character count at the top, the data all imports.
May 26, 2021 at 7:05 pm
I appreciate the response, but unfortunately, this didn't work. The first file, with the "dummy" data loaded great. I then changed the setting on the Excel Source step to "False" for ValidateExternalMetadata, and ran the code again, and got the truncation error.
Bummer. Also try setting the package property 'DelayValidation' to True (after importing the wide column file and checking, using the Advanced Editor, that the columns in the External Columns collection are now wide enough.
If this doesn't work, post back and I'll try to make some time to have a go at this myself. I've done it before, but the interaction between Excel/ACE and SSIS is such a pain, there may be more to it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 26, 2021 at 8:27 pm
Thank you again for the response, but unfortunately, setting the "DelayValidation" to True didn't help either.
When I go into Advanced Editor - Input and Output Columns for my Excel Source, all the Properties for my text fields in "Output Columns" are set to "Unicode String [DT_WSTR] with Length: 4000". All of the "External Columns" are showing "DataType: Unicode String [DT_WSTR] with a Length: 255." However, if I change the Length in "External Columns" to "4000", the values do not stay and always revert back to 255.
What is very odd, is that when I use the original file that was provided by the user, I get the "truncation" error for my column. But if I take this same file, and sort it by the LEN(), with the largest first, the data imports. This is what is so confusing, but I can't dictate to the end user they must add a LEN() column, sort by that before sending to us to import.
If I take my "Dummy File" that has the first 1,000 rows padded to 4000 characters, the data imports with no issue. If I use the original file, sorted by character length, the data imports. If I use the original file provided by the user, I get truncation errors because it seems like SSIS "guesses" what the lengths should be based on a small sampling of data instead of using what we tell it the field lengths should be. However, in a file with 35,000 records, most do not come close to the 4000 character limit, and the odds of SSIS picking those rose is slim to none.
May 26, 2021 at 8:44 pm
I also noticed though, if I set the Error Output to ignore truncation errors, I can process the original file as well, but it only imports the first 255 characters, not the 3000+ characters.
This is a known issue with Excel - and the available solutions I found are:
The problem is that the Excel source will reset the value as soon as it validates the external meta data, which occurs at execution time if you set either of the properties (DelayValidation or ValidateExternalMetaData) to false.
I will add a final option - one I don't know will work but may give you better control - is to use a script component and c# to read the Excel file. This probably will require a separate library, but there are quite a few available - and there might be some already available directly in .NET.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2021 at 2:45 am
Thanks for the information. I do appreciate it. One thought I had, but I am not sure how to go about executing it, or if it would even work. In my SSIS package, could I add a row of data to Excel that is "dummy" data as the first row, then import all the data, then add a step at the end that would delete this data from the table? Or have a step in my SSIS package that converts the Excel file to a *.csv file?
The reason I ask this is because I cannot change any registry values (I looked into this, but due to PC security, I do not have admin access to change this on my PC) and we try not to make the users involved anymore than providing the file. I mean technically, we could ask them to provide the data as a *.csv file, or to sort the data first, but it would be nice to have a solution in place to minimize user input.
May 27, 2021 at 9:37 am
I just played around with this scenario for an hour and I was unable to come up with a solution which works in all cases. Very irritating.
Automating an export to CSV is possible, as is adding a 'fake' row 1 containing wide data, though you would probably need to use the Excel interop to do so (this is not a great solution as it requires the server to perform 'Excel automation').
As Jeffrey mentioned, there are libraries available which allow more-direct access to Excel, bypassing some of the ACE driver's limitations.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 27, 2021 at 12:14 pm
Phil: thank you for taking the time to try. I appreciate it immensely.
Jeff: thank you for all your help as well. I cannot thank you enough for the time and effort you put in trying to help solve this for me.
I appreciate all the help the two of you have provided. Thank you
May 28, 2021 at 6:16 pm
I had a random thought on this, in that I can sort my query to the Excel file by the LEN() of the field causing the issue such as: SELECT * FROM [Data$A5:DU] ORDER BY LEN([Just]) DESC, but now that causes an error of the following:
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Record is too large.".
I have seen that updating the JET provider to 12.0 helps with this, but unfortunately, "The 'microsoft.jet.oledb.12.0' provider is not registered on the local machine." so I cannot go this route. If it would help solve this issue, I could try to get it installed, but that isn't always easy due to corporate policy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply