SSIS and HEX conversion.

[TonFrere]

Hello,
I must convert a CSV file which is simply hexadecimal words. There is a SSIS function to convert from decimal to hex but none for either hex to decimal or hex to ASCII.
Here is what my file looks like:
3032;3631;3031
3032;3631;3032

Here is what it should give:
026101
026102

I already started a DataFlow task which seperates each hex combo into different columns and get rid of the semicolons. The next step would have been to convert each column contain into ASCII digits but to my surprise, there is no function to do that.

Is there a way to:
- Create my own SSIS function and use it in the Derived Column tool.
- Process all rows given by my DataFlow task into C# or VB?

Or maybe I'm just doing this wrong from the start.

Any thoughts?
Justin

[ck9663]

Can CONVERT() handle it?

-- CK

[TonFrere]

Can CONVERT() handle it?

-- CK

I can't seem to find CONVERT() in the list of SSIS functions. What I did was to create an Script Component (duh!) and convert each column using VB code. I just wish I could find a more user-friendly solution.

Here is the VB code (thanks to google):
Row.Digit1 = Chr(CInt(Val("&H" & Row.Digit1.ToString()))) 'Digit1 is the name of the column
[...]

The CInt function was recommended by VS auto-corrector(??). Without it I had this error: "Option Strict On disallows implicit conversions from 'Double' to 'Integer'." I must admit I don't know why but it works so, what the hell!

Can I use C# code in an SSIS script?
Justin

[srinig]

Hi,

I got similar situation to convert to hex...I believe we can use c# script with in SSIS.

I am new to c#, Can you share your script here ?

[cactusdata]

Use Substring or other method to split your string into its single values:

30, 32, 36, 31, etc.

Now, convert each of these, here '36' to return '6':

Char(Convert(int,Convert(binary(1), '0x' + '36', 1)))

Then concatenate these single characters to build the full string.

[KRITGuy]

I use this in my Classic ASP. Oftentimes I transfer Notes from a Memo Field in a VB App to an Access Database on a simple IIS HTTP Server.

So that sort of data is expanded to 7-bit and hexed.

In the ASP Page it's converted with my HexToASCII function:

Cheers,
Kevin

<%
FUNCTION HexToASCII (Param)
HexToAscii = Param
Command = ""
BYTES = LEN(Param)
FOR i = 1 to BYTES
HV = "&H" & MID(Param,i,2)
HB = CInt(HV)
Command = Command & CHR(HB)
i = i + 1
NEXT
HexToAscii = Command
END FUNCTION
%>