I'm currently working on a project to receive data from a legacy mainframe system and load this data into SQL Server Tables. The source system that will be providing the data is written in assembler. Every field on the file is in hex format. One of the files we'll receive has three (3) fields on it.
I thought it would be easier to show using a cobol copybook. The layout on the mainframe looks like this:
01 Hexbit-File. 05 Hexbit1 PIC X. 05 Hexbit2 PIC X. 05 Hexbit3 PIC X.
View of Actual record from source file. So going by the copybook Hexbit1 = x'E1' , Hexbit2 = x'C0' , Hexbit3 = x'80' :
UTF-8 View of contents of the file after ftp to server from mainframe in binary: xE1xC0x80
Question - The file has been ftp'd in binary over to the server. Is it possible to read in the text file using SSIS and convert each hex field value to an 8 bit binary value?
Expected results:
Field Hexbit1
value on mainframe file - x'E1' expected binary value stored on target table as 11100001
Field Hexbit2
value on mainframe file - x'C0' expected binary value stored on target table as 11000000
Field Hexbit3
value on mainframe file - x'80' expected binary value stored on target table as 10000000
Thank you in advance.