Generating a flat file

Hi ya,

I'm generating a flat file from SSIS package and i'm having some problems.

My package contains this Data Flow which is connecting to a Database and importing the records. I did design a script component since the file I generate has some padding.

Here is the code for the component so that you really understand what i'm trying to achieve over here.

Dim toPadTo As Int32

Dim myDate As String

Dim MultVal As Int64

Dim myMonth As Int16, myMonth2 As String

myMonth = CShort(Row.TransDate.Month)

If myMonth <= 9 Then

myMonth2 = myMonth.ToString.PadLeft(2, CChar("0"))

Else

myMonth2 = myMonth.ToString

End If

myDate = CStr(Row.TransDate.Date.Day) + myMonth2 + CStr(Row.TransDate.Year)

MultVal = CInt(Row.TransAmount * 100)

Dim myChkLength As Int16 = CShort(MultVal.ToString.Length)

Dim myCombine As String = CStr(MultVal) & CStr(myDate)

Dim myCombine2 As String = CStr(MultVal) & CStr(myDate)

Select Case myChkLength

Case 4

toPadTo = 30 - (myCombine.ToString.Length)

Case 5

toPadTo = 30 - (myCombine.ToString.Length - 1)

Case 6

toPadTo = 30 - (myCombine.ToString.Length - 2)

Case 7

toPadTo = 30 - (myCombine.ToString.Length - 3)

End Select

' + myDate.ToString.Length - 1))

Row.myConvert = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim MyNewRow As String = myCombine2.PadLeft(toPadTo, CChar("0"))

Dim ChkSign As Int16

ChkSign = CShort(Math.Sign(CDec(MyNewRow)))

If ChkSign = 1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

ElseIf ChkSign = -1 Then

Row.myConvert = MyNewRow.PadLeft(2, CChar("-"))

Else

Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))

End If

End Sub

Obviously i don't think this is the best approach hence i'm asking? not to mention the sort of problem i'm getting with the + and - insertion part of the code.

To give you an example of how the value would be:

Actual value: 159.23

After transformation it should be like this : +00000000000015923

Depending on the amount x no zeros should be inserted.

Any other way to achieve it? Apart from this I also need to generate a sequence no with some string at the end of the ragged file. How would i go for it ?

Cheers

Rizshe

Read the data in from the SQL database, add a script transformation using the input column, add an output column of type string length 30, transform like: -

Dim Style As String = "+0000000000000000000000000000#;" _
+ "-0000000000000000000000000000#;" _
+ "+0000000000000000000000000000#"
with row
dim n as int = CInt(.myoldcolumn * 100)
.mynewcolumn = Format(n, Style)
end with

then simply output the new columns to the flat file

|||

Hi Paul,

Your code doesn't seem to do anything different then my own above. Perhaps you misunderstood me.

I would need to put the 0 padding and depending on the + or - in the amount column also put the sign.

The problem i'm getting is that the amount varies from 0.94 to 124.78 and i would need to pad it accordingly.

Cheers

Rizwan

|||

Rizwan,

I may have missed something, but I believe that the code does exactly that. it creates 30 character strings with the correct sign...

|||

Hi Paul,

My deepest apologies. I must be blind that i didn't look at your code carefully.

Yes it does work.

Thank you very much and again forgive me for my stupid mistake

Rizwan