Link to home
Create AccountLog in
Avatar of Rmumpower00
Rmumpower00

asked on

Why is there a Comma being input in the TXT output file?

I've created a DTSx Package that runs the following Query:

SELECT '00000123456789002'+RIGHT ('00000000' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 15)
+ CONVERT(varchar(10),CHECKDT, 112)+ RIGHT ('0000000' + REPLACE (CAST(checknet AS VARCHAR(20)), '.',  ''), 10)+CASE(ckstatus)
WHEN 'O' THEN '' WHEN 'V' THEN 'C' END + '' as Val
FROM dbo.CHK

WHERE checkdate BETWEEN '03/14/12' and '03/14/12' and bankid='MYBANK'and ISNUMERIC(checkno)=1

The Results are put into a Txt File

At the end of the results it's adding a ,
000001234567890020000000020201203140000058878C,

Can anyone tell me why this is happening and how I can fix it?
Avatar of lcohan
lcohan
Flag of Canada image

Perhaps it is a CSV text that you selected in the SSIS package.
You could manually try to export something like :

exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on; SELECT ''00000123456789002''+RIGHT (''00000000'' + LTRIM(CAST(CHECKNO AS VARCHAR(20))), 15) + CONVERT(varchar(10),CHECKDT, 112)+ RIGHT (''0000000'' + REPLACE (CAST(checknet AS VARCHAR(20)), ''.'',  ''''), 10)+CASE(ckstatus) WHEN ''O'' THEN '''' WHEN ''V'' THEN ''C'' END + '''' as Val
FROM dbo.CHK
WHERE checkdate BETWEEN ''03/14/12'' and ''03/14/12'' and bankid=''MYBANK''and ISNUMERIC(checkno)=1;" -W -o"c:\output.txt"'
Avatar of Rmumpower00
Rmumpower00

ASKER

So if I ran that query it would export it to a Text?
ASKER CERTIFIED SOLUTION
Avatar of Simone B
Simone B
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I'll do that now Buttercup, I think you're dead on!
That was it, Thanks a ton!
Excellent! Happy to help.