December 4, 2013 at 2:50 am
HI ,
While Importing data from MS_Excel to SQL DB by using import and export wizard i am getting below error (Text was truncated or one or more characters had no match in the target code page)
I have max data length in column 2000 and i am creating table from Import wizard only(not mapped to existing table)
How can import max length column to DB by using Import Wizard?
Messages
Error 0xc020901c: Data Flow Task 1: There was an error with Source - Properties_te$.Outputs[Excel Source Output].Columns[Value (in English)] on Source - Properties_te$.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.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - Properties_te$.Outputs[Excel Source Output].Columns[Value (in English)]" failed because truncation occurred, and the truncation row disposition on "Source - Properties_te$.Outputs[Excel Source Output].Columns[Value (in English)]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
December 4, 2013 at 4:50 am
HI All,
I tried different scenarios i.e. By using Import wizard and SSIS but it is same issue at finally Imported successfully by using Bulk Insert command
Ex: BULK INSERT CSVTest FROM 'D:\18022013.Txt'
WITH
(
FIELDTERMINATOR = ''
)
December 4, 2013 at 5:26 am
In SSIS Max is considered as "DT_TEXT", and the destination Column SHOULD BE "DT_TEXT" to aviod truncation. otherwise you have to implicitly convert the "DT_TEXT" to "DT_STR" or "DT_WSTR".
FOR FLAT File, SSIS can limit the data while defining the columns. if so then there shouldn't be any issue while importing it to the destination.
December 4, 2013 at 5:58 am
twin.devil (12/4/2013)
In SSIS Max is considered as "DT_TEXT", and the destination Column SHOULD BE "DT_TEXT" to aviod truncation. otherwise you have to implicitly convert the "DT_TEXT" to "DT_STR" or "DT_WSTR".FOR FLAT File, SSIS can limit the data while defining the columns. if so then there shouldn't be any issue while importing it to the destination.
It is returning error in first step only how i can use convert transformation?
This is my data-flow design
1. Excel Data source --Getting data from excel source
2. Convert Transformation
3. Oledb destination
Error It is returning step1 only.
This is error:
[Excel Source [24]] Error: There was an error with Excel Source.Outputs[Excel Source Output].Columns[Value (in English)] on Excel Source.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.".
December 4, 2013 at 6:10 am
Did you set the length in both the input and output properties for the column?
Far away is close at hand in the images of elsewhere.
Anon.
December 4, 2013 at 6:21 am
David Burrows (12/4/2013)
Did you set the length in both the input and output properties for the column?
+1, i think you have many set the length of the output columns of the source If its true, then you need to set the ErrorOutput of your source, in "truncate" column From "Fail component" to "Redirectrow" to get the faulty rows
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply