November 18, 2016 at 8:05 am
I have a simple package that takes data from a SPROC and puts it in a .CSV file.
Until today, it ran without issue.
The error it's now throwing is
"The data conversion for column "Phone1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
Now all my text columns in my SPROC and my package metadata are NVARCHAR/WSTR.
I tracked the issue down to a single record.
In this record, somebody had entered "?+" before the numeric phone number.
Removing the "?" fixed the package.
My question is, why does the package choke on the "?+" combination? It's just text. I've had no luck with Google so hopefully somebody can explain this behaviour to me.
November 18, 2016 at 8:16 am
planetmatt (11/18/2016)
I have a simple package that takes data from a SPROC and puts it in a .CSV file.Until today, it ran without issue.
The error it's now throwing is
"The data conversion for column "Phone1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
Now all my text columns in my SPROC and my package metadata are NVARCHAR/WSTR.
I tracked the issue down to a single record.
In this record, somebody had entered "?+" before the numeric phone number.
Removing the "?" fixed the package.
My question is, why does the package choke on the "?+" combination? It's just text. I've had no luck with Google so hopefully somebody can explain this behaviour to me.
What's the length of the problem data item?
What's the length of the column in the SSIS package?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2016 at 8:18 am
Phil Parkin (11/18/2016)
planetmatt (11/18/2016)
I have a simple package that takes data from a SPROC and puts it in a .CSV file.Until today, it ran without issue.
The error it's now throwing is
"The data conversion for column "Phone1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
Now all my text columns in my SPROC and my package metadata are NVARCHAR/WSTR.
I tracked the issue down to a single record.
In this record, somebody had entered "?+" before the numeric phone number.
Removing the "?" fixed the package.
My question is, why does the package choke on the "?+" combination? It's just text. I've had no luck with Google so hopefully somebody can explain this behaviour to me.
What's the length of the problem data item?
What's the length of the column in the SSIS package?
100 characters is the length in the package metadata and SQL field length.
The data itself is about 15 characters. Standard phone number + the ?+ prefix.
November 18, 2016 at 8:20 am
I think that the ? could be a representation of a strange character, have you tried to reproduce the error by entering a ? in front of a phone number and see if it fails?
😎
November 18, 2016 at 8:21 am
Another weird thing to throw in the mix.
When examining the raw data in SQL, SELECTing the row in question with a SELECT * does not show the "?". Only the "+9999 9999 9999" shows up and the question mark is nowhere to be seen
But, if I explicitly convert the NVARCHAR to a VARCHAR, then Management Studio displays "?+9999 9999 9999"
November 18, 2016 at 8:22 am
--Edit: removed silly question
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2016 at 8:26 am
planetmatt (11/18/2016)
Another weird thing to throw in the mix.When examining the raw data in SQL, SELECTing the row in question with a SELECT * does not show the "?". Only the "+9999 9999 9999" shows up and the question mark is nowhere to be seen
But, if I explicitly convert the NVARCHAR to a VARCHAR, then Management Studio displays "?+9999 9999 9999"
If you convert to varbinary, what do you get?
😎
November 18, 2016 at 8:26 am
I suspect that the ? is not a ? in the data ... it's some other non-printable character.
This code snippet shows that ?+ is no problem:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULL
DROP TABLE #test;
CREATE TABLE #test (Phone NVARCHAR(100));
INSERT #test
(
Phone
)
VALUES (N'?+9999 9999 9999');
SELECT * FROM #test t
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2016 at 8:30 am
Phil Parkin (11/18/2016)
I suspect that the ? is not a ? in the data ... it's some other non-printable character.This code snippet shows that ?+ is no problem:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULLDROP TABLE #test;
CREATE TABLE #test (Phone NVARCHAR(100));
INSERT #test
(
Phone
)
VALUES (N'?+9999 9999 9999');
SELECT * FROM #test t
Yes, I think that although it appears to be a "?", it's not. I think an end user copy and pasted a character that looks like a ? into our application with the phone number from a website and that put it into the database.
The Flat file destination is set to CODEPAGE 1242 ANSI LATIN so I'm guessing the character was not from this code page which results in the error.
But I'm not a UNICODE guy so I'm not certain.
If this is the case, how do I handle this in SSIS?
November 18, 2016 at 8:42 am
Phil Parkin (11/18/2016)
I suspect that the ? is not a ? in the data ... it's some other non-printable character.This code snippet shows that ?+ is no problem:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULLDROP TABLE #test;
CREATE TABLE #test (Phone NVARCHAR(100));
INSERT #test
(
Phone
)
VALUES (N'?+9999 9999 9999');
SELECT * FROM #test t
Good point. I'd check what the ASCII codes are for the first few characters in that data.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 18, 2016 at 9:15 am
planetmatt (11/18/2016)
Phil Parkin (11/18/2016)
I suspect that the ? is not a ? in the data ... it's some other non-printable character.This code snippet shows that ?+ is no problem:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULLDROP TABLE #test;
CREATE TABLE #test (Phone NVARCHAR(100));
INSERT #test
(
Phone
)
VALUES (N'?+9999 9999 9999');
SELECT * FROM #test t
Yes, I think that although it appears to be a "?", it's not. I think an end user copy and pasted a character that looks like a ? into our application with the phone number from a website and that put it into the database.
The Flat file destination is set to CODEPAGE 1242 ANSI LATIN so I'm guessing the character was not from this code page which results in the error.
But I'm not a UNICODE guy so I'm not certain.
If this is the case, how do I handle this in SSIS?
Option 1: add more code in your proc to strip out any rubbish characters
Option 2: add a script component in your data flow & write some C# to remove rubbish characters
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 18, 2016 at 9:21 am
Phil Parkin (11/18/2016)
planetmatt (11/18/2016)
Phil Parkin (11/18/2016)
I suspect that the ? is not a ? in the data ... it's some other non-printable character.This code snippet shows that ?+ is no problem:
IF OBJECT_ID('tempdb..#test', 'U') IS NOT NULLDROP TABLE #test;
CREATE TABLE #test (Phone NVARCHAR(100));
INSERT #test
(
Phone
)
VALUES (N'?+9999 9999 9999');
SELECT * FROM #test t
Yes, I think that although it appears to be a "?", it's not. I think an end user copy and pasted a character that looks like a ? into our application with the phone number from a website and that put it into the database.
The Flat file destination is set to CODEPAGE 1242 ANSI LATIN so I'm guessing the character was not from this code page which results in the error.
But I'm not a UNICODE guy so I'm not certain.
If this is the case, how do I handle this in SSIS?
Option 1: add more code in your proc to strip out any rubbish characters
Option 2: add a script component in your data flow & write some C# to remove rubbish characters
No, I found the easy option.
Every thing was Unicode right up until the Flat File Connection where I had neglected to tick the "Unicode" tick box :blush:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply