How to skip last row in the SSIS data flow

Question

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 1

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

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

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

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

11. 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
    

12. Add a Conditional Split Next to the Script Component

13. Split Rows using the Following Expression

    [IsLastRow] == False
    

14. Add the OLEDB Destination next to the conditional Split

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

Answer 2

If your requirement is to avoid rows having null values in the flat file then you can follow below approach,

• Read data from flat file using source component.
• Use Conditional Split component, and in the case expression provide as !ISNULL(Column1) && !ISNULL(Column2) (Column1 and Column2 can be as your wish. If your flat file has a column named, say ID and it does not have null value except the last row, then you can use as !ISNULL(ID)).
• Map the case output to the OLEDB destination.

Hope this would help you a lot.