question

elinzeyjr-7790 avatar image
0 Votes"
elinzeyjr-7790 asked elinzeyjr-7790 commented

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' :

Command ===


000001 ÷{Ø
EC8
100



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.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered elinzeyjr-7790 commented

Hi @elinzeyjr-7790 ,

It will be more convenient to use t-sql query to get the desired output.

 create function fn__IntToBit(@t int)
 returns varchar(300)
 as
 begin
     declare @info varchar(300);
     set @info=''
     while (@t>0)
     begin
         select @info=cast(@t%2 as varchar)+@info,@t=@t/2
     end 
     return(@info)
 end
 go
    
 declare @col1 varchar(50);
 set @col1 = 'E1';
 select dbo.fn__IntToBit(convert(int, convert(varbinary, @col1, 2))) as col1;
 go
 declare @col2 varchar(50);
 set @col2 = 'C0';
 select dbo.fn__IntToBit(convert(int, convert(varbinary, @col2, 2))) as col2;
 go
 declare @col3 varchar(50);
 set @col3 = '80';
 select dbo.fn__IntToBit(convert(int, convert(varbinary, @col3, 2))) as col3;
 go

91158-ssms-sqlquery.png

Notes:
-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))
-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

Best regards,
Mona



If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.






ssms-sqlquery.png (44.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @elinzeyjr-7790 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·

Mona,
Thank you for the response to my question. I apologize for the delay, I was pulled in to another project. When I specify x'E1' in my description x represent hex so E1 is not a 2 character field. It is actually a 1 character field that contains the hex value of E1. It looks like the solution is expecting a 2 character field of 'E1'. If that is the case, I would have to figure out how to get the hex value to a 2 character field and then input that value into the function. I'm not sure that would solve the problem since I need to go from hex to binary instead of hex to ascii to binary. I dont' believe that the hex value of E1 will have the same binary value of a character value of E1.

Thank You

Elinzeyjr

0 Votes 0 ·