Hi Phil,
I had to make few changes in my script component code, so as to generate only one consolidated output file. 🙂
If you had seen my code earlier i had created both filestream & streamwriter object inside the sub module 'Input0_ProcessInputRow', now i have added two new submodule one is 'Pre execute and another is 'PostExecute'. Inside PreExecute i tried to create new object for filestream & streamwriter and inside postexecute sub module i tried to dispose the objects i have created.
here is my updated code in script component:
Option Explicit Off
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Text
Public Class ScriptMain
Inherits UserComponent
Dim strtarget As String
Dim FileFormat As String
Dim fPHTaxReport As FileStream
Dim swPHTaxWriter As StreamWriter
Dim UTF8Encoding As Encoding
Public Overrides Sub PreExecute()
FileFormat = Me.Variables.Varfileformat.ToString()
strtarget = Me.Variables.VarTargetFilepath.ToString + "Consolidated Report to PH Tax Department_" + DateTime.Now.ToString("yyyyMMddhhmm") + FileFormat
fPHTaxReport = New FileStream(strtarget, FileMode.Append, FileAccess.Write)
swPHTaxWriter = New StreamWriter(fPHTaxReport, UTF8Encoding.UTF8)
MyBase.PreExecute()
End Sub
Public Overrides Sub PostExecute()
swPHTaxWriter.Flush()
fPHTaxReport.Close()
MyBase.PostExecute()
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.RecordType = "H" Then
swPHTaxWriter.WriteLine(Row.RecordType + "|" + _
Row.TransactionType + "|" + _
Row.TINNumber + "|" + _
Row.OwnersRegisteredName + "|" + _
Row.OwnersLastName + "|" + _
Row.OwnersFirstName + "|" + _
Row.OwnersMiddleName + "|" + _
Row.OwnersTradeName + "|" + _
Row.OwnersAddress1 + "|" + _
Row.OwnersAddress2 + "|" + _
CStr(Row.ExemptSales) + "|" + _
CStr(Row.ZeroRatedSales) + "|" + _
CStr(Row.TaxableSales) + "|" + _
CStr(Row.OutputTax) + "|" + _
Row.RDOCode + "|" + _
Row.TaxableMonth + "|" + _
Row.FiscalYearEnding)
ElseIf (Row.RecordType = "D") Then
swPHTaxWriter.WriteLine(Row.RecordType + "|" + _
Row.TransactionType + "|" + _
Row.TINNumber + "|" + _
Row.OwnersRegisteredName + "|" + _
Row.OwnersLastName + "|" + _
Row.OwnersFirstName + "|" + _
Row.OwnersMiddleName + "|" + _
Row.OwnersAddress1 + "|" + _
Row.OwnersAddress2 + "|" + _
CStr(Row.ExemptSales) + "|" + _
CStr(Row.ZeroRatedSales) + "|" + _
CStr(Row.TaxableSales) + "|" + _
CStr(Row.OutputTax) + "|" + _
Row.RDOCode + "|" + _
Row.TaxableMonth)
End If
End Sub
End Class
One quick observation i found:
if i run the old code for record count less than 800 than i'm getting 1 o/p file, if its more than 1000 then i'm getting multiple output file.
But with the above updated code there don't seems to be any issue. Withn 'N' no. of records coming frm the source i'm getting only one o/p file created in the shared path.
Not sure what exactly is ithe issue with script component....
Is it something to do with no of records cached in the buffers???
Any idea
Thanks
Sam