Sql Server Table- Spl Character - Warning msgs & Phantom

PeteM2 · Post by **PeteM2** » Tue Apr 07, 2015 6:14 am

Hi,

In my server job the source table is a Sql server table and one of the column contains Special characters (Var Char field). To handle the special characters am using the Convert() function to transform those special characters to spaces. Though few special characters are getting transformed by the Convert function correctly few are not(especailly ? is not getting transformed). Also I still get a warning mesage as below:

"ds_nlsmap() - BCI NLS input mapping error, row 3234, column detail_text
data '/Group/Group Security & Fraud/SMART/3B SENIOR AND FOREIGN ?50K DAILY/'
external 2F47726F75702F47726F757020536563757269747920262046726175642F534D4152542F33422053454E494F5220414E4420464F524549474E20A335304B204441494C592F"

and finally also get a DataStage Phanthom error as given below.

DataStage Job 1132 Phantom 1190

Can someone help me with the convert funtion and also to avoid getting the above mentioned warning & phantom message.

I am using Datastage 9.1 version.

PeteM2 · Post by **PeteM2** » Wed Apr 08, 2015 7:10 am

Hi Craig,

Thanks for your response.

Well, the field which I read is a field where the user enters free text. So any type of text is expected. Even non printable characters are also expected in that filed. I have a custom function in the job to handle that non printable characters as well.

I would be happy if you can help me in handling those special characters.

All I want is, the spl characters should he handled ,WITHOUT warning messages or Phanthom errors in DS director.

Thanks.

PeteM2 · Post by **PeteM2** » Thu Apr 09, 2015 12:56 am

Mine is a simple job.

Job type: Server
Source: Sql server table stage
Datatype: Varchar - Length 4000

In the transfromer - I am using the custom defined funtion to handle nonprintable characters and replacing with spaces. Also trying to using Convert function to convert spl characters to space but not been succssfull as explained earlier.

Output Stage: Hash file
Date type: LongNVarchar.

In toto, in one of the Varchar column's in Sql server table stage, user can input any non printable characters and any special characters (Its a free text field). I need to read that field, convert the nonprintable characters to spaces and then read the special characters and output the same special characters in the target stage (Hash file) without any warning messages or Phanthom Errors. In the job we have a limit of 350 warnings set, above which the job will fail. So if there are more than 350 rows of data with spl characters inthe table, my job is throwing 350 warnings and then fails. This is my issue.

Request your help in fixing the issue.

Note:

I had used double convert fuction as per your suggetsion and tried to handle spl characters but still getting errors but not phanthom warnings.

Convert(Convert("?&^%/\$)(*}{[]~#","",Field name),"",Field name)

Error:
JObname: ds_nlsmap() - BCI NLS input mapping error, row 68826, column detail_text
data 'MAF ?'
external 4D414620A3

chulett · Post by **chulett** » Thu Apr 09, 2015 6:58 am

You've got the "double convert" coded incorrectly. What you use for the list of characters are the GOOD ones, the ones to keep which is a finite list. Anything that does not match that list is what ends up being converted.

PeteM2 · Post by **PeteM2** » Fri Apr 10, 2015 6:13 am

I tried to replace all non pritable characters and spl characters to a blank space.
I used the below code and still getting warnings:

Convert(Convert("1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz","",Field name),"",Field name)

Warning msg:

BCI NLS input mapping error, row 55357, column detail_text
data '? Insured (Conv)'
external A320496E73757265642028436F6E7629.

wondering why alwasys I get a warning msg when there is a ? mark in the input data.