CODEWITHSUNDEEP
sql-serverssisetlssis-2012dataflow
VHK
VHK

Reputation: 193

How to skip last row in the SSIS data flow

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

Upvotes: 7

Views: 12786

Answers (2)

Hadi
Hadi

Reputation: 37348

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

Upvotes: 6

Viki888
Viki888

Reputation: 2774

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.

Upvotes: 2

Related Questions