SQLTeam.com | Weblogs | Forums

Is it possible to convert hexbit fields to binary through SSIS?

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.