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

Garfish

This might can help you : social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Krisha

Answer 1

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.

Camacho

Answer 2

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

Starvation