How to skip last row in the SSIS data flow

VHK picture VHK · Mar 7, 2017 · Viewed 7.8k times · Source

I am using FlatFile Source Manager --> Script COmponent as Trans --> OLEDB destination in my data flow.

Source reads all the rows from flat file and i want to skip the last row (Trailer record) updating the database.

Since it contains the NULL values, database throws error.

Please assist me how to resolve this.

Regards, VHK

Answer

Hadi picture Hadi · Mar 7, 2017

To ignore the last row you have to do the following steps:

  1. Add a DataFlow Task (let's name it DFT RowCount)
  2. Add a Global Variable of Type System.Int32 (Name: User::RowCount)
  3. In this DataFlow Task add a Flat File Source (The file you want to import)
  4. Add a RowCount component next to the Flat File Source
  5. Map the RowCount result to the variable User::RowCount

enter image description here

  1. Add Another DataFlow Task (let's name it DFT Import)

enter image description here

  1. In DFT Import add a Flat File Source (File you need to Import)
  2. Add a Script Component next to the Flat File Source
  3. Add User::RowCount Variable to the Script ReadOnly Variables

enter image description here

  1. Add an Output Column of type DT_BOOL (Name: IsLastRow)

enter image description here

  1. In the Script Window write the following Script

    Dim intRowCount As Integer = 0
    Dim intCurrentRow As Integer = 0
    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        intRowCount = Variables.RowCount
    End Sub
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        intCurrentRow += 1
    
        If intCurrentRow = intRowCount Then
            Row.IsLastRow = True
        Else
            Row.IsLastRow = False
        End If
    
    End Sub
    
  2. Add a Conditional Split Next to the Script Component

  3. Split Rows using the Following Expression

    [IsLastRow] == False
    

enter image description here

  1. Add the OLEDB Destination next to the conditional Split

enter image description here

Side Note: if you want to ignore rows for another case (not last row) just change the script writen in the script component to meet your requirements