EBCDIC, packed, zoned fields?

[sumgirl]

Hello all. At my shop we handle loads of data from clients running on a variety of platforms. We would like to import the data fiels we receive into our sql server database, but currently we have issues with files that come with packed decimals, comp3, zoned decimals, ebcdic and etc...from IBM minis and mainframes.

My question...how are folks handling these data types? Currently, we load the data onto a unix box and do some preprocessing and then dts up to sql, but we want to cut the unix piece out of the process altogether.

 

[sguslan]

As your data is not in any standard format, i.e. ASCII flat file/delimited/comma separated or Access/Excel, you need to do either of the following:

1-Build inteligence in the source server to convert into an ASCII format
2-In DTS, code a VBScript ActiveX intelligence to do
A. Convert character data from EBCDIC
B. Convert the Decimal type data to ASCII
C. Save the data into a file and load it into SQL from
the file

More, if the data is in DB2 , why not read it directly from DB2/DRDA, or if the source data is in VSam files, then use MS Host Integratin Server 2000 to connect to the IBM box, and use MS provided OLEDB drivers for Vsam files.
________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[sumgirl]

No dice on any of the above. I am receiving files from clients and have no way to directly connect to client systems. Also, I was hoping someone might direct me to some code - I know I could use dts if I had source to do the translations.

-thanks anyway

 

[datamat]

If you are inclined to spend a little $, You can get a low end ETL such as Data Junction to do this quite simply. It's not a free solution but it won't cost much either. I scan download an eval copy from their site. It will read EBCDIC code pages and unpack packed decimal, comp-3 type data.

 

[sguslan]

Well, if you have any C programmer, i am sure it is a nor brainer to crank some code tod do it. I've written utilities in C that does similar chores to read data exported by DB2 QMF. I am not in the office now , otherwise i would post it for you. When back in office after 8 days you can reach me at sguslan@yahoo.com. I'll be glad to share the code with you. If the source data is from DB2, all you have to do is get the people who create the files to do a QMF export for you. Or, if data is in a flat file format the utility can be easily modified for the purpose ..
Good luck.
Datamat's suggestion is a great value if uou can spend money for it... ________________________________________________________________________________
If you do not like change, get out of the IT business...