.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Text was truncated with "nvarchar(max)" data type and size by using SSIS Import and Export Wizar

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server


I am using SSIS Import and export Wizar to load data from Excel file, I have set the datatype as "nvarchar(max); those data are not biger than 1000 bits but was failed to transfer as the error says:

[Source - TreatmentPlans$ [1]] Error: The "output column "SchoolPlan" (57)" failed because truncation occurred, and the truncation row disposition on "output column "SchoolPlan" (57)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

View Complete Post

More Related Resource Links

Text truncated to 255 using SSIS ACE.oledbprovider to import excel 2007 spreadsheet

Hi All, Trying to use SSIS SQL 2005 to import an Excel 2007 file (.xlsx). I'm using Office 12.0 Access Database Engine OLEDB Provider with extended properties Excel 12.0;HDR=Yes;IMEX=1 The problem is when I have text with more than 255 characters. It basically defaults the source output to Unicode string[DT_WSTR] 255, If I change it it errors out. I have done the registry change according to http://msdn.microsoft.com/en-us/library/ms141683.aspx but I think it only applies to Excel source not OleDb Source. (A Excel source does give me a text instead of string )   I have also tried to increasing the length of output such as 4000 instead of 255 but it doesn't allow either.   Anybody can help?I'm stuck for a day. Thank you very much for your help. Regards, Koala  

SSIS Text was truncated with status value 4

I am developing a SSIS package, trying to update an existing SQL table from a CSV flat file. All of the columns are successfully updating except for one column. If I ignore this column on truncate, my package completes successfully. So I know this is a truncate problem and not error. This column is empty for almost every row. However, there are a few rows where this field is 200-300 characters. My data conversion task identified this field as a DT_WSTR, but from what I've read elsewhere maybe this should be DT_NTEXT. I've tried both and I even set the DT_WSTR to 500. But none of this fixed my problem. How can I fix? What data type should this column be in my SQL table? Error: 0xC02020A1 at Data Flow Task 1, Source - Berkeley812_csv [1]: Data conversion failed. The data conversion for column "Reason for Delay in Transition" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". Error: 0xC020902A at Data Flow Task 1, Source - Berkeley812_csv [1]: The "output column "Reason for Delay in Transition" (110)" failed because truncation occurred, and the truncation row disposition on "output column "Reason for Delay in Transition" (110)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. Error

SSIS Import/Export Wizard: "Unexpected Unrecoverable Error"

For whatever reason, whenever the SSIS Import/Export Wizard needs to display an error message, it throws up "Unexpected Unrecoverable Error" instead. The three buttons - Abort, Retry, and Ignore - all cause the wizard to close. I see some activity in Process Monitor that looks like DTSWizard.exe is probing around for some System.Web assemblies, and not finding them. I'm running 2008 SP1 on Win 7 x86, and I've reinstalled the client tools and Integration Services. Anybody else seen this issue? Should I file a report on Connect?

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

How to export SQL Data to XML via SSIS

Hi, Is there a sample or anything that shows what the best way of exporting SQL data to XML?Thanks,David

data type problem in import data from excel file


Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)

Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1


Multidimensional Arrays (or) ArrayLists and Object Data Type in SSIS

I want to create a multidimensional array dynamically in a Script Component and assign to a Object DataType and then use that variable in another Script Component, to retrieve the elements of the multidimensional array

SSIS 2008 data type bug



I am using SSIS 2008, I set a varaible @t1 to data type CHAR and set the value to Y next I used an Execute SQL Task with an ADO.NET connection to SQL 2008 R2 database to insert this the value of @t1 into a test table with only 1 column of data type NCHAR(1):

insert into test1

On the Parameter Mapping section of this task I put the Data Type as String and initially set the Parameter size to -1  

The only problem is it tries to insert a 2 character numeric value into the SQL table. If I set the value of @t1 to N the parameter mapping seems to convert this to 78, when I set the value of @t1 to Y the parameter mapping converts this to 89 it looks like it converts the character to an ASCII code.

Is this meant to be the intended behaviour, as all I wanted to do was insert the actual character into the destination column and not the ASCII code?



Import and Export data between servers


I hope this is the right place to ask.

I am conencting two different sql server with different IPs. I want to copy some of data from one server table to another server table. I saw from my friend there was Import and Export Data from Tools menu (he said that he added before), he wrote ips and then choose tables and columns ... I couldn't find this right now in Management Studio. Where is it? I anyone can help me, i will be appreciated.


I am using sql server 2005 standart edition



Ahmet UZUN

Import and Export List data in excel sheet


Hi ,

I need to import based selective data from excel sheet and export data from sharepoint list, I'm new in Sharepoint.

can anybody guide me? What all possible ways are there and which would be the best.



Checking Mapping -- SSIS package created Export and Import


Hi all

I created SSIS package using Import and Export,

1.some  of the reasons I what edit that Package to add more tables and modify tables names.

2.I want check the mapping Data types mached and not match(Basically I want check mapped Data types)

can body help me hwo to check the mapping and edit the ssis package


Thanks in Advance




SSIS Error Text was truncated or one or more characters had no match in the target code page


I the same issue or something close.

Except I have one Field (27) that get a trunacation error


Data conversion failed. The data conversion for column "Column 27" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

The "output column "Column 27" (91)" failed because truncation occurred, and the truncation row disposition on "output column "Column 27" (91)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.


Data looks like:Red Text is the field that is throwing the error!





"RUTH           ",

"EDWARDS             ",









"1447203880    ",


Import and Export Data (32-bit) Wizard to Dump Data to Excel


I am trying to export some data from a simple three-column table on my SQL 2008 R2 instance to Excel 2007 (also happens in Excel 2003). Our data has a lot of foreign (Hebrew) characters on it and although the copy works ok, the data as it appears in Excel is gibberish.

The source table in SQL contains three columns:

  • smallint - numbers
  • varchar(30) - hebrew text
  • varchar(50) - hebrew text
  • varchar(15) - hebrew text

When I fire up the Import-Export wizard I see the following: http://img524.imageshack.us/img524/1830/sqltable.png. When I click on the yellow warning for a conversion problem, I see this: http://img375.imageshack.us/img375/4255/conversion.png.

Do I need to modify the core SQL or change the data type? I already tried modifying the destination datatype to be varchar instead of LongText but that didn't help.

Any assistance would be fantastic!


Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend