March 19, 2012 at 10:19 am
I'm getting an error when importing from AS400 on a column that is defined as 70 Characters in the AS400 and the matching column in SQL Server.
I performed a MAX Length on the column and I get a length of 70.
I knew that expanding the column was not going to resolve the error but I dropped the table anyway and defined the column as VARCHAR(100).
I had the batch size set to 500 and the package fails with a record count of 9,847.
Any help would be greatly appreciated.
The column status returned was: "Text was truncated or one or more characters had no match in the target code page
The "output column "COMMSG2" (56)" failed because truncation occurred, and the truncation row disposition on "output column "COMMSG2" (56)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 11:12 am
I'm getting the same error on a different table.
It get the following truncation error:
[ctl_additional_interest [1]] Error: The "output column "DESC0LINE1" (70)" failed because truncation occurred, and the truncation row disposition on "output column "DESC0LINE1" (70)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
The column length is defined as 30 Characters in the source and destination columns I did a MAX(LENGTH(DESCLINE1)) and it returned 30.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 11:21 am
Hi,
From what you have described I don't think this is an issue with the size of the fields but with the data itself and that SQL cannot convert the data in the fields to the database.
I'd suggest that you change the field to a NVARCHAR, as this will allow more characters.
You may also need to look at the collation of the database too.
Regards,
Graham
March 19, 2012 at 11:24 am
GRussell31 (3/19/2012)
Hi,From what you have described I don't think this is an issue with the size of the fields but with the data itself and that SQL cannot convert the data in the fields to the database.
I'd suggest that you change the field to a NVARCHAR, as this will allow more characters.
You may also need to look at the collation of the database too.
Regards,
Graham
Should I be using a CAST Function or something?
It works when it is populated with an Open Query Statement but it is slow.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 11:29 am
Hiya,
I haven't tried importing data via Open Query so can't really comment, sorry.
If you set the underlying table to be a NVARCHAR you shouldn't need to use a cast or convert as the SSIS meta data should pick up the change.
Hope that helps,
Graham
March 19, 2012 at 11:45 am
I will give it a shot.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 11:54 am
GRussell31 (3/19/2012)
If you set the underlying table to be a NVARCHAR you shouldn't need to use a cast or convert as the SSIS meta data should pick up the change.
I tried the NVARCHAR and I still get the error.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 12:10 pm
NVARCHAR(2000) does not work.
I suspect that there is an issue with the data.
SQL Server does not like something about a particular record.
I need to figure out how to redirect the offending record(s) to a file.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2012 at 5:02 am
What data is in the file, is it in a foreign language?
What collation is the database?
Graham
March 20, 2012 at 5:21 am
GRussell31 (3/20/2012)
What data is in the file, is it in a foreign language?What collation is the database?
Graham
It is English.
There are comments, payment, financial information, etc
The tables load using OPEN QUERY.
The collation is: SQL_Latin1_General_CP1_CI_AS
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2012 at 2:58 pm
How can I ignore truncation?
[CTL_Accounts_Payable [1]] Error: The "output column "ADD0LINE03" (199)" failed because truncation occurred, and the truncation row disposition on "output column "ADD0LINE03" (199)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply