SSIS - How to skip last row in the SSIS data flow

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 written in the script component to meet your requirements

Comments

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

Don’t install Hadoop on Windows!

SSIS - script component DateTime Formats Implicit Conversion