Control characters(Junk chars) in flat files from SQL server

sethuraman · Post by **sethuraman** » Tue Apr 10, 2012 1:16 am

Hi,

I just extract data from a SQL server table (MS SQL) and write it in a flat file.

My job design is

ODBC Conn ---> CPY ---> SeqFile

I have 50 columns in it. after successful run, I get some control characters in the file like ^C or ^R. Be it Fixed width or Delimited, the file is not getting created with given no. of columns or in given length. No clue what could be the issue.

Please help it out.

Regards,
Sethuraman.

pandeesh · Post by **pandeesh** » Tue Apr 10, 2012 1:35 am

Check whetehr the control charcater is already there in database itself,
How you are viewing sequential file>? inside datastage or unix?

sethuraman · Post by **sethuraman** » Tue Apr 10, 2012 2:59 am

Hi, I have checked in the DB, no control characters found over there. i am viewing the file in Unix, VI editor mode.

pandeesh · Post by **pandeesh** » Tue Apr 10, 2012 3:47 am

What you are getting if you use

Code: Select all

cat filename

The control chars are at the end of the line or some where else?
How you have configured the Sequential file stage?
Whats the delimter you have specified?

sethuraman · Post by **sethuraman** » Tue Apr 10, 2012 4:15 am

if i use cat <file name> then i find no control characters. but if i open it in vi <file name> mode then it appears in front of few fields. I create file in Fixedwith mode. also i have tried creating comma seperated delimited file. nothing returns the exact output.

pandeesh · Post by **pandeesh** » Tue Apr 10, 2012 4:23 am

It will be great if you post sample data what you are getting while using vi

sethuraman · Post by **sethuraman** » Tue Apr 10, 2012 4:30 am

pandeesh wrote:What you are getting if you use
Code: Select all
cat filename
The control chars are at the end of the line or some where else?
How you have configured the Sequential file stage?
Whats the delimter you have specified?

if i use cat <file name> then i find no control characters. but if i open it in vi <file name> mode then it appears in front of few fields. I create file in Fixedwith mode. also i have tried creating comma seperated delimited file. nothing returns the exact output.

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 10, 2012 4:44 am

what is the value of APT_STRING_PADCHAR environment variable and what is the data type of the field where you see the junk characters?

sethuraman · Post by **sethuraman** » Tue Apr 10, 2012 4:56 am

priyadarshikunal wrote:what is the value of APT_STRING_PADCHAR environment variable and what is the data type of the field where you see the junk characters?

Value for the param APT_STRING_PADCHAR = 0x20.

I get the junk characters for all kind of datatypes like varchar, char, smallint.

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 10, 2012 5:23 am

In anycase the smallint field should not have control characters, check the format options in seq file stage and check your extraction query as well if any kind of concatanation or padding is used.

BTW, which SQL Server version we are talking about here?