read cobol comp-3 field

[Mecn]

Hi,

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

Thanks,

[Payson]

On Dec 23, 10:44 am, "Mecn" <m...@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

[Tom Cooper]

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" <pays...@hotmail.com> wrote in message
news:3d16da8c-3430-48b3...@22g2000yqr.googlegroups.com...

[--CELKO--]

I am with Tom and Payson on this, but does the COBOL have an ODBC or
something to connect to SQLs?

[Tony Rogerson]

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" <me...@yahoo.com> wrote in message
news:#2b5Pb#gKHA...@TK2MSFTNGP06.phx.gbl...

[Mecn]

Thanks all

"Tony Rogerson" <tonyro...@torver.net> wrote in message
news:1BAF8C81-3A65-492B...@microsoft.com...

[Payson]

> 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.  >

I thought about this some over the holiday. (I need a life :) ). I
probably didn't emphasize the problems caused by the EBCDIC to ASCII
translation as much as I should have.

Here is an example. In packed decimal, the number +500 would be
represented as x'500C'. The character x'50' in EBCDIC is the
ampersand ('&'). When this data is transliterated to ASCII, the &
character will become its ASCII equivilant - x'26'. Therefore, the
packed decimal number becomes x'260C' = +260.

I hope this makes sense. Moral - be careful with downloaded mainframe
data.

Payson