Prev: tsql to change oracle pswd
Next: Wierd Performance Question- Please help!!
From: Mecn on 23 Dec 2009 10:44
Hi,

Anyone here knows how to read a flat file with Cobol comp-3 field from sql
server.

Thanks,


From: Payson on 23 Dec 2009 16:49
On Dec 23, 10:44 am, "Mecn" <m...(a)yahoo.com> wrote:

> Anyone here knows how to read a flat file with Cobol comp-3 field from sql
> server.


I'm real sure there is no good way to translate a comp-3 (packed
decimal) field. It's simpler to modify the cobol program to output a
display field.
Use "PIC ----------9.99" or something similar.

Maybe there is something in SSIS. I doubt it, but maybe...

You could probably kludge something together using the ascii()
function and looping through the characters, but I wouldn't want to do
it. If you simply must, BOL has an example to get you started. See
"Ascii function" However, if the file originally came from an IBM
mainframe (EBCDIC), some of the characters would have been translated
to their ASCII equivalents in the download. Ugly stuff. SQL is not
built for this.

For those who are unfamiliar with packed decimal, it is a numeric
field with a digit stuffed into every half-byte and a sign (x'C' for
positive, x'D' for negative and x'F' for no sign) in the rightmost
half-byte.

There's bound to be some sort of creative solution to this I haven't
thought of...

Best

Payson
From: Tom Cooper on 23 Dec 2009 21:36
I agree with Payson, the best possible solution is to get the provider of
this file to output the field as DISPLAY.

If that's not possible/feasible, maybe you could get a cheap COBOL compiler
and write a program to input the file and then output a file converting the
COMP-3 to DISPLAY. That's trivial to do in COBOL.

If you don't like that idea, you probably can use SSIS. AFAIK, there is no
direct ability to do this in SSIS, but you should be able to write a
transform to do this. Microsoft has a sample SSIS component at
http://www.microsoft.com/downloads/details.aspx?familyid=0e4bba52-cc52-4d89-8590-cda297ff7fbd&displaylang=en
that may get you started.

I don't no how difficult the transform will be. Whenever this came up in my
work, I was always able to kick, scream, and hold my breath until management
agreed to do one of the first two options.

Tom

"Payson" <payson_b(a)hotmail.com> wrote in message
news:3d16da8c-3430-48b3-9a8f-a8e67b9ce877(a)22g2000yqr.googlegroups.com...
On Dec 23, 10:44 am, "Mecn" <m...(a)yahoo.com> wrote:

> Anyone here knows how to read a flat file with Cobol comp-3 field from sql
> server.


I'm real sure there is no good way to translate a comp-3 (packed
decimal) field. It's simpler to modify the cobol program to output a
display field.
Use "PIC ----------9.99" or something similar.

Maybe there is something in SSIS. I doubt it, but maybe...

You could probably kludge something together using the ascii()
function and looping through the characters, but I wouldn't want to do
it. If you simply must, BOL has an example to get you started. See
"Ascii function" However, if the file originally came from an IBM
mainframe (EBCDIC), some of the characters would have been translated
to their ASCII equivalents in the download. Ugly stuff. SQL is not
built for this.

For those who are unfamiliar with packed decimal, it is a numeric
field with a digit stuffed into every half-byte and a sign (x'C' for
positive, x'D' for negative and x'F' for no sign) in the rightmost
half-byte.

There's bound to be some sort of creative solution to this I haven't
thought of...

Best

Payson

From: --CELKO-- on 24 Dec 2009 00:50
I am with Tom and Payson on this, but does the COBOL have an ODBC or
something to connect to SQLs?
From: Tony Rogerson on 24 Dec 2009 02:26
Hi Mecn,

You need to use SSIS (SQL Server Integration Services).

This will help:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/463cd34f-1c1e-441a-b262-0a4745f139e9

Many thanks,
Tony.


"Mecn" <mecn(a)yahoo.com> wrote in message
news:#2b5Pb#gKHA.1536(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> Anyone here knows how to read a flat file with Cobol comp-3 field from sql
> server.
>
> Thanks,
>
 |  Next  |  Last
Pages: 1 2
Prev: tsql to change oracle pswd
Next: Wierd Performance Question- Please help!!