Need Help writing SSIS logic

Question

Post reply

Need Help writing SSIS logic

Reena-875121

Newbie

Points: 3
More actions
May 29, 2009 at 1:30 pm

#130904

I have a column in a flat file which is an 2power(x) integer.I need to convert this integer into binary number and push into different columns into a sql database table.
e.g.
2pow3=8 =1000.I need to push 1 into a column,0 into another,0 into another and 0 into another
i have the max number as 2pow15.How to do this using SSIS.Please Help!!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Does the source contain a string: "2pow3" or an integer: 8?

You say integer, so I will assume 8. Just looking at it now ...

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

I'll show you how to do the conversion part.

Add a new string field to your pipeline - I used 'BinaryString'.

Then add a script component. The line that does the conversion is

Row.BinaryString = Convert.ToString(Row.Integer, 2)

where 'Integer' is the name of the integer field which needs to be converted.

From there, you just need to split out the individual characters of the new 'BinaryString' field to get what you want.

Phil

Reena-875121 Newbie Points: 3 More actions · Answer 3

Thank you phil,this will output my integer to binary but i am confused how to split the binary into various columns in the output table.

e.g

source column -->Binary conv-->dest col1 | dest col 2| dest col 3| dest col 4

8.........................1000 .............1...............0................0..................0

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

Instead of adding only 1 new field to the pipeline, add enough char(1) fields to accommodate your binary characters.

Then assign the fields in the script component. This example shows you how to loop through all the different characters in a string field and should give you enough.

Dim i As Int32, BinaryString As String, Bin1 As Char

i = 0

BinaryString = Convert.ToString(Row.Integer, 2)

Do While i < BinaryString.Length

Bin1 = BinaryString(i)

MsgBox(Bin1)

i += 1

Loop

End Sub

Phil

Reena-875121 Newbie Points: 3 More actions · Answer 5

phil,

I tried the above suggestion and when i see my output columns all values are '0'.Am i doing anything wrong.Please help!!

Dim i As Int32, BinaryString As String, Bin1 As Char, Bin2 As Char, Bin3 As Char, Bin4 As Char, Bin5 As Char, Bin6 As Char, Bin7 As Char, Bin8 As Char, Bin9 As Char, test As Integer

test = CType(Row.COLOR, Integer)

i = 0

BinaryString = Convert.ToString(test, 2)

Do While i < BinaryString.Length

Bin1 = BinaryString(i)

Bin2 = BinaryString(i)

Bin3 = BinaryString(i)

Bin4 = BinaryString(i)

Bin5 = BinaryString(i)

Bin6 = BinaryString(i)

Bin7 = BinaryString(i)

Bin8 = BinaryString(i)

Bin9 = BinaryString(i)

i += 1

Loop

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

I'm afraid that your logic is not quite sound 🙂

You are setting Bin1, Bin2, ..., Bin9 to the same value for each letter in the binary string - so they will all always be the same value (they could all be one - if the binary number ended in '1').

Try this version instead:

Dim BinaryString As String, Bin1 As Char, Bin2 As Char, Bin3 As Char, Bin4 As Char, Bin5 As Char, Bin6 As Char, Bin7 As Char, Bin8 As Char, Bin9 As Char, test As Integer

BinaryString = Convert.ToString(Row.Integer, 2)

If BinaryString.Length > 0 Then Bin1 = BinaryString(0)

If BinaryString.Length > 1 Then Bin2 = BinaryString(1)

If BinaryString.Length > 2 Then Bin3 = BinaryString(2)

If BinaryString.Length > 3 Then Bin4 = BinaryString(3)

If BinaryString.Length > 4 Then Bin5 = BinaryString(4)

If BinaryString.Length > 5 Then Bin6 = BinaryString(5)

If BinaryString.Length > 6 Then Bin7 = BinaryString(6)

If BinaryString.Length > 7 Then Bin8 = BinaryString(7)

If BinaryString.Length > 8 Then Bin9 = BinaryString(8)

Phil