· Hi All, I am skipping the header record in flat file connection manager HVWBB630 201012302010123005473333 <<<--- Header Iseyapcm |88071523|E|Y|N|N|N|Y| Anitakaul1 |88072163|E|Y|N|N|N|Y| efarrellssb |88072233|E|Y|N|N|N|Y| markghouse |88072450|E|Y|N|N|N|Y| dechopra |88074988|E|Y|N|N|N|Y| TVWBB630 00000005122010123020101230TRADE PROFILE <<<--- Trailer VWBB630 is program name 0000000512 is record count 20101230 date For flat file data in one table.trailer record in another table. How to load the trailer record in table using script component. Program name,recordcount and date in table..please suggest me in this issue Thanks CMK

OK, go with Script Component as Source . This is whole solution: create new data flow task, add a script component transform and set it as Source then in second tab ( inputs and outputs ), click on output0, and rename it as OutputMainRows then add 8 columns of type DT_STR under OutputMainRows, name them Column 0, Column 1, Column 2,Column 3,...,Column 7 then add another output, name it as OutputTrailerRow under OutputTrailerRow, add 3 columns: column name datatype ----------------------------- ProgramName DT_STR RecordCount DT_I4 Date DT_DATE then go to script tab, set language as visual basic then click on edit script, this is whole code in the script ( you can copy and paste it instead existing code there if you use SSIS 2008 ): ' Microsoft SQL Server Integration Services Script Component ' Write scripts using Microsoft Visual Basic 2008. ' ScriptMain is the entry point class of the script. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False )> _ Public Class ScriptMain Inherits UserComponent Dim sr As System.IO.StreamReader Public Overrides Sub PreExecute() MyBase .PreExecute() sr = New System.IO.StreamReader("E:\test\flatfilewithHandT.txt" ) End Sub Public Overrides Sub PostExecute() MyBase .PostExecute() sr.Close() End Sub Public Overrides Sub CreateNewOutputRows() Dim lineIndex As Integer = 0 While (Not sr.EndOfStream) Dim line As String = sr.ReadLine() 'MessageBox.Show("inside while") If (lineIndex <> 0) Then 'remove header row 'MessageBox.Show("inside if") Dim columnArray As String () = line.Split(Convert.ToChar("|" )) If (columnArray.Length > 1) Then 'main rows OutputMainRowsBuffer.AddRow() OutputMainRowsBuffer.Column0 = columnArray(0) OutputMainRowsBuffer.Column1 = columnArray(1) OutputMainRowsBuffer.Column2 = columnArray(2) OutputMainRowsBuffer.Column3 = columnArray(3) OutputMainRowsBuffer.Column4 = columnArray(4) OutputMainRowsBuffer.Column5 = columnArray(5) OutputMainRowsBuffer.Column6 = columnArray(6) OutputMainRowsBuffer.Column7 = columnArray(7) Else 'trailer row OutputTrailerRowBuffer.AddRow() OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7) OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10) OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), "yyyyMMdd" , New System.Globalization.CultureInfo("en-US" )) End If End If lineIndex = lineIndex + 1 End While End Sub End Class save and close it. now the script component has two output, you can connect OutputMainRows to your destination table for main rows and connectio OuputTrailerRow to the destination where you want to insert trailer values. That's all.http://www.rad.pasfu.com

OK, go with Script Component as Source . This is whole solution: create new data flow task, add a script component transform and set it as Source then in second tab ( inputs and outputs ), click on output0, and rename it as OutputMainRows then add 8 columns of type DT_STR under OutputMainRows, name them Column 0, Column 1, Column 2,Column 3,...,Column 7 then add another output, name it as OutputTrailerRow under OutputTrailerRow, add 3 columns: column name datatype ----------------------------- ProgramName DT_STR RecordCount DT_I4 Date DT_DATE then go to script tab, set language as visual basic then click on edit script, this is whole code in the script ( you can copy and paste it instead existing code there if you use SSIS 2008 ): ' Microsoft SQL Server Integration Services Script Component ' Write scripts using Microsoft Visual Basic 2008. ' ScriptMain is the entry point class of the script. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False )> _ Public Class ScriptMain Inherits UserComponent Dim sr As System.IO.StreamReader Public Overrides Sub PreExecute() MyBase .PreExecute() sr = New System.IO.StreamReader("E:\test\flatfilewithHandT.txt" ) End Sub Public Overrides Sub PostExecute() MyBase .PostExecute() sr.Close() End Sub Public Overrides Sub CreateNewOutputRows() Dim lineIndex As Integer = 0 While (Not sr.EndOfStream) Dim line As String = sr.ReadLine() 'MessageBox.Show("inside while") If (lineIndex <> 0) Then 'remove header row 'MessageBox.Show("inside if") Dim columnArray As String () = line.Split(Convert.ToChar("|" )) If (columnArray.Length > 1) Then 'main rows OutputMainRowsBuffer.AddRow() OutputMainRowsBuffer.Column0 = columnArray(0) OutputMainRowsBuffer.Column1 = columnArray(1) OutputMainRowsBuffer.Column2 = columnArray(2) OutputMainRowsBuffer.Column3 = columnArray(3) OutputMainRowsBuffer.Column4 = columnArray(4) OutputMainRowsBuffer.Column5 = columnArray(5) OutputMainRowsBuffer.Column6 = columnArray(6) OutputMainRowsBuffer.Column7 = columnArray(7) Else 'trailer row OutputTrailerRowBuffer.AddRow() OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7) OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10) OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), "yyyyMMdd" , New System.Globalization.CultureInfo("en-US" )) End If End If lineIndex = lineIndex + 1 End While End Sub End Class save and close it. now the script component has two output, you can connect OutputMainRows to your destination table for main rows and connectio OuputTrailerRow to the destination where you want to insert trailer values. That's all.http://www.rad.pasfu.com

OK, go with Script Component as Source . This is whole solution: create new data flow task, add a script component transform and set it as Source then in second tab ( inputs and outputs ), click on output0, and rename it as OutputMainRows then add 8 columns of type DT_STR under OutputMainRows, name them Column 0, Column 1, Column 2,Column 3,...,Column 7 then add another output, name it as OutputTrailerRow under OutputTrailerRow, add 3 columns: column name datatype ----------------------------- ProgramName DT_STR RecordCount DT_I4 Date DT_DATE then go to script tab, set language as visual basic then click on edit script, this is whole code in the script ( you can copy and paste it instead existing code there if you use SSIS 2008 ): ' Microsoft SQL Server Integration Services Script Component ' Write scripts using Microsoft Visual Basic 2008. ' ScriptMain is the entry point class of the script. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False )> _ Public Class ScriptMain Inherits UserComponent Dim sr As System.IO.StreamReader Public Overrides Sub PreExecute() MyBase .PreExecute() sr = New System.IO.StreamReader("E:\test\flatfilewithHandT.txt" ) End Sub Public Overrides Sub PostExecute() MyBase .PostExecute() sr.Close() End Sub Public Overrides Sub CreateNewOutputRows() Dim lineIndex As Integer = 0 While (Not sr.EndOfStream) Dim line As String = sr.ReadLine() 'MessageBox.Show("inside while") If (lineIndex <> 0) Then 'remove header row 'MessageBox.Show("inside if") Dim columnArray As String () = line.Split(Convert.ToChar("|" )) If (columnArray.Length > 1) Then 'main rows OutputMainRowsBuffer.AddRow() OutputMainRowsBuffer.Column0 = columnArray(0) OutputMainRowsBuffer.Column1 = columnArray(1) OutputMainRowsBuffer.Column2 = columnArray(2) OutputMainRowsBuffer.Column3 = columnArray(3) OutputMainRowsBuffer.Column4 = columnArray(4) OutputMainRowsBuffer.Column5 = columnArray(5) OutputMainRowsBuffer.Column6 = columnArray(6) OutputMainRowsBuffer.Column7 = columnArray(7) Else 'trailer row OutputTrailerRowBuffer.AddRow() OutputTrailerRowBuffer.ProgramName = line.Substring(1, 7) OutputTrailerRowBuffer.RecordCount = line.Substring(9, 10) OutputTrailerRowBuffer.Date = DateTime.ParseExact(line.Substring(19, 8), "yyyyMMdd" , New System.Globalization.CultureInfo("en-US" )) End If End If lineIndex = lineIndex + 1 End While End Sub End Class save and close it. now the script component has two output, you can connect OutputMainRows to your destination table for main rows and connectio OuputTrailerRow to the destination where you want to insert trailer values. That's all.http://www.rad.pasfu.com

Reza Raad its working fine if we use the script component as source. If I take flat file source I want load the data in one table and trailer record another table how can I implement above situation Thanks CMK...

In Above solution we are taking Script component as source,now i am trying that task using flat file source... how to load the normal data and trailer records in 2 diffrent destinations. Thanks CMK...

In Above solution we are taking Script component as source,now i am trying that task using flat file source... how to load the normal data and trailer records in 2 diffrent destinations. Thanks CMK...