January 22, 2016 at 6:33 pm
Hello,
I occasionally get this error "Text was truncated or one or more characters had no match in the target code page.", when using importing data with SSIandExport Wizard. This is due to a column having more than 255 characters (nvarchar). I tried under column mappings to manual edit the length from 255 to 500, after manually pulling up file in excel running len function to see max character length is less than 500.
My question is how can I fix this error without manually editing data in original file to be less than or = 255 char? Editing column length to 500 varchar, didn't prevent error from going when i tried re-importing.
Thanks
January 22, 2016 at 9:17 pm
What kind of file is it? a CSV you opened in Excel or is it an Excel Workbook (xls or xlsx)?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 22, 2016 at 9:21 pm
.xls file even changed varchar max, no luck
January 22, 2016 at 9:27 pm
Try NVARCHAR(MAX).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 22, 2016 at 9:41 pm
same error with nvarchar (max)
January 22, 2016 at 10:03 pm
Could there be special characters in the original file im trying to import causing this like "" < > etc, if so which characters should i be looking for to remove, thanks?
January 23, 2016 at 2:20 am
it is either character encoding issue or length causing truncation. can you post a Workbook (with sensitive data masked) tha causes the issue on your side that I can use to repro the issue and I'll try it out on my side?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 23, 2016 at 7:05 am
Ok I found the solution - I think. I had to filter out the data in the column in .xls that was over 255 characters. Then modified data to be at or under 255 char. It started importing now, but errors out at a specific row, so now at least i can narrow down further, before wasn't even giving row and wasn't importing anything at all.
I was hoping to avoid modifying data as solution. My question is why doesn't the nvarchar(max) or the ignore button in the wizard in for truncation (as next step) in wizard do anything?
January 23, 2016 at 7:22 am
VegasL (1/23/2016)
Ok I found the solution - I think. I had to filter out the data in the column in .xls that was over 255 characters. Then modified data to be at or under 255 char. It started importing now, but errors out at a specific row, so now at least i can narrow down further, before wasn't even giving row and wasn't importing anything at all.I was hoping to avoid modifying data as solution. My question is why doesn't the nvarchar(max) or the ignore button in the wizard in for truncation (as next step) in wizard do anything?
can you please post as Orlando suggested...a sample workbook that represents your problem.
we cant see your data and can only make educated guesses with out a sample.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 23, 2016 at 9:12 am
The data type of the destination is not the only thing at play there.
I'd double-check the OutputColumnWidth defined for the source columns (in the Wizard, when you choose a flat file source, you'll see General, Columns, Advanced, and Preview tabs. Go to the Advanced tab; there you'll see SSIS datatypes and OutputColumnWidth for each of the source columns).
Make sure OutputColumnWidth is defined to be wide enough for your data. The most likely cause of your problem is that the destination columns are long enough, but the OutputColumnWidth is too small for your data.
Cheers!
January 23, 2016 at 9:57 am
In addition to what Jacob said, I'd leave the column defined as an Nvarchar(MAX) like Orlando suggested. Excel supports Unicode very well, so you should allow for the possibility. Otherwise you could end up with a bunch of "??? ?? ? ??????" in your table.
January 23, 2016 at 12:02 pm
Thanks Jacob, J Livingston, & Ed - I am using nvarchar(max) all the time for the column the error report mentions. - still errors during import..
@JLivingston - The sample workbook is too many data types for to figure out what is causing it..I wouldn't know where to begin to narrow down..would probably waste everyone's time...but thanks..
I'd double-check the OutputColumnWidth defined for the source columns (in the Wizard, when you choose a flat file source, you'll see General, Columns, Advanced, and Preview tabs. Go to the Advanced tab; there you'll see SSIS datatypes and OutputColumnWidth for each of the source columns).
I'm using SSMS, I right click DB,>Tasks>Import Data>SS Import & Export Wizard appears>Next>I select xls file from Data Source>Next>Next>I choose the table from the xls file (to import)> I see Edit Mappings / Preview > Next.
I just don't see this...
(in the Wizard, when you choose a flat file source, you'll see General, Columns, Advanced, and Preview tabs. Go to the Advanced tab; there you'll see SSIS datatypes and OutputColumnWidth for each of the source columns)
Where is this and could you give detailed steps on how to get where you're saying in wizard pls?
Thanks
January 23, 2016 at 2:38 pm
Ah, you're using an Excel source, not a flat file source.
That's a documented "feature" of using an Excel source (under "Usage Considerations", see "Truncated Text" and "Data Types" sections here: https://msdn.microsoft.com/en-us/library/ms141683.aspx) . When using an Excel source with the wizard, you don't get to modify the datatype of the source columns. The type for the source columns is determined automatically by scanning the first few rows of the source.
In the case of character types, if it doesn't find anything greater than 255 in those scanned rows, it will assume the source column has a length of 255.
There are a couple workarounds, both mentioned in the Microsoft link above. You can fiddle with the registry to make it scan more rows when determining the data type (not my favorite), or you can just put dummy strings with length>255 on the first row. That way when it scans the first few rows, it'll see that it needs the source data type to have a length greater than 255.
Cheers!
January 23, 2016 at 2:56 pm
I am pretty sure you will not be able to see the Output Column options in the Import/Export Wizard. Before you complete the wizard, is there an option to save the Package to disk. You will need to get yourself a copy of SSDT so you can develop on the SSIS Package resulting from the Wizard, or develop a new Package from scratch, to see all the options. Bing for SSDT. It is free and if you do not have Visual Studio newer versions will even install the Shell for you. Look into getting the version of SSDT that matches up to your version of SQL Server, in case you every want to run your Packages in an unattended way from your server, you can do that in the future.
There is a possibility you are experiencing a known issue where the Excel OLE DB driver tries to infer data types by reading the first n-rows of each column. The default was 8 rows for some of the older drivers, not sure what the default is for your case. You have to make a registry change to increase the number of rows. On which row is your long-text value?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 23, 2016 at 9:40 pm
Jacob Wilkins (1/23/2016)
Ah, you're using an Excel source, not a flat file source.That's a documented "feature" of using an Excel source (under "Usage Considerations", see "Truncated Text" and "Data Types" sections here: https://msdn.microsoft.com/en-us/library/ms141683.aspx) . When using an Excel source with the wizard, you don't get to modify the datatype of the source columns. The type for the source columns is determined automatically by scanning the first few rows of the source.
In the case of character types, if it doesn't find anything greater than 255 in those scanned rows, it will assume the source column has a length of 255.
There are a couple workarounds, both mentioned in the Microsoft link above. You can fiddle with the registry to make it scan more rows when determining the data type (not my favorite), or you can just put dummy strings with length>255 on the first row. That way when it scans the first few rows, it'll see that it needs the source data type to have a length greater than 255.
Cheers!
Jacob --- This is absolutely amazing. Thanks so much for you're help. I can't believe I've been grappling with this issue for years. So I tried with and without malformed data, with and without the regedit changes, and basically all I did was I added a dummy row of data in the columns that were initially effected, put some really long data in the xls file, then imported with no problem. SQL Server automatically put nvarchar(max).
Thanks to everyone else as well! Really appreciate it!!!!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply