August 21, 2008 at 8:41 am
Okie here is my package brief desciption. I select records as SQL command, output to flat file. I have a record count too. Then I add a footer to it with script Source and then Header too. Header works fine , Footers works fine too, but i need to pad off with zero till 9 char.
let's say if record count is 500, then when i write to footer
I have to write as T000000500.Here is the script as a sourceImports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim recordCount As Integer
Dim vars As IDTSVariables90
'Get the record count
Me.VariableDispenser.LockOneForRead("RecordCount", vars)
recordCount = CType(vars("RecordCount").Value, Integer) + 2 ' 2 is added for header count and trailer count.
vars.Unlock()
'Output one row with record count
Output0Buffer.AddRow()
Output0Buffer.FooterRow = String.Format("T{0}", recordCount)
Output0Buffer.SetEndOfRowset()
End Sub
End Class
If u see above I have T then followed by recordCount, so If record count is 10056 then I should have T000010056. I can only get T10056.
The other way I am thinking is to write expression in the record count variable through some Right function. ( this is how i have done in my regular query of mine, like this---
select right('0000000000' + cast(cast(99.99* 100 AS integer) as varchar(12)), 12)
August 21, 2008 at 8:56 am
Your thinking is correct but you have to work with the 0's as strings and not ints.
0000000 + 1056 = 1056.
"0000000" + CSTR(1056) = "0000001056"
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 9:23 am
Do you suggest me to do with Expression in variabales or do the coding in the script. I tried with variables ---
I declared another variable CountForWrite and whitin that varaiable i had expression
RIGHT("000000000" + (DT_STR, 9 , 1252) @[User::RecordCount] , 9)
where recordcount is my actual recoundcount
Then I altered the script to use this countForWrite
Still, gave me T302 instaed of T000000302
August 21, 2008 at 9:30 am
You can do it in a variable or in the script. If you adding the footer in the script, I would do it in there. No point in pushing the value out to ssis only to pull it back in.
As for the results, what you have is perfect. Works fine for me.
Can you show a screen dump of your expression.
Still, do it in VB if it is used within the script.
VB:
Dim sss As Int32 = 1234
Dim dd As String = (sss.ToString).PadLeft(10, "0")
MsgBox(dd)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 21, 2008 at 9:55 am
Help me out here .....( i am very bad at coding..............)
How would you do that exactly in the code.
Output0Buffer.AddRow()
Output0Buffer.FooterRow = String.Format("T{0}", recordCount)
Output0Buffer.SetEndOfRowset()
I tried several here and there doesn't work.....
Thanks,
August 21, 2008 at 11:39 am
There actually is a padleft and padright property in vb.net that's great. So for example:
Dim MyValue as string = "test"
MyValue = MyValue.ToString.Padleft(5,"0")
will keep padding the value to a field length of 5. So the above value would look like: "0test"
Dim MyValue as string = "Y"
MyValue = MyValue.ToString.Padleft(10,"0")
would look like:
"000000000Y"
Strick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply