I have an excel file with four text columns: one of them is called ShortDescription which has the longest value. I created a table in SQL Server 2008 database, with four columns and the ShortDescription column type is set to NvarChar(Max).
but when using the SSIS import and export dialog, I keep getting the mentioned error in the title, even when I set the OnTruncation option to Ignore.
I tried to clear the column data, and it succeeded (so I made sure that the problem is in the ShortDescription column). I tried to copy the whole data to another excel work book, and still no luck.
any ideas ???
A simple way to get it to work is edit the file you want to import and create a new row in the first spot. That way it will always be sampled. Then for any columns that may have >255 characters, just add 255 characters to the cell and it will work. After you import, just delete out the junk row you added.
I assume you're trying to import this using an Excel Source in the SSIS dialog?
If so, the problem is probably that SSIS samples some number of rows at the beginning of your spreadsheet when it creates the Excel source. If on the [ShortDescription] column it doesn't notice anything too large, it will default to a 255 character text column.
So to import data from a column that contains rows with large amounts of data without truncation, there are two options:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
(of if your system is x64 then under theHKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Jet\4.0\Engines\Excel
) registry key.You can see more information at these two links:
To further explain, SSIS creates 3 objects behind the scenes of the wizard, an Excel data source object, a SQL table destination object, and a data flow operator between them. The Excel source object defines the source data and exists independent of the other two objects. So when it's created, the sampling I described is done and the source column size is set. So by the time the data flow operator executes and tries to pull the data from excel to put in your table, it's already looking at a data source that's been limited to 255 characters.
I got this error when I was trying to import a large file that had some chinese characters in it, and also some invalid (large) strings.
The text file was saved in UTF8 format.
My settings:
On the General Option (didn't change anything):
There is an Advanced Option on the left
On the Review Data Type Mapping
My target column had width = 50.
I got no errors with these settings.
For me this link helped me : https://support.microsoft.com/en-us/kb/189897
I had this issue when importing from a flat, delimited file into SQL Server. The solution was to update the 'OutputColumnWidth' value for the offending column (from the error message). On the 'Choose a Data Source' form in the import wizard, my source was the flat file. On the leftmost pane, choose 'Advanced'. You can then set the properties of individual columns. In my case, the 'OutputColumnWidth' for most of my columns was defaulted to '50'. I simply updated it to a larger value that would not truncate the value from the flat file.
There is an alternative location of the registry component that needs to be changed to resolve this problem.
If you cannot find it at
then look in