SSIS - Get last line in a flat file

Posted on 2012-09-17
Last Modified: 2013-09-01
Hi Experts ,

I need to process a file and get the last record and then process it. The last line is like a footer record which i need to match with the rest of the file. I'm just trying to get a handle on the best way to do this,

I thought of storing the information in a record set destination etc, or maybe something similar,

Potentially SSIS might not even be the best tool for the job in this case, maybe a script task instead might be worthwhile.
Question by:adammet04
    LVL 37

    Expert Comment

    If you've got a way of distinguishing the last row from the others (based on layout), perhaps the principle of splitting the flow can be used here?  Check out this article for details:

    If the last row cannot be distinguished, have a look at this method:
    (found through
    LVL 12

    Accepted Solution

    I have a lot of text files where I have to process the last row in a unique way.
    This method has worked for me, and I bet it does for you too.

    In your ssis script task, set your output to non-synchronous.

    Then do something like this:

    Public Class ScriptMain
     Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
            While Buffer.NextRow()
            End While
            If Buffer.EndOfRowset Then
                Process the last row
           End If
    End Sub
    Public Overrides Sub CreateNewOutputRows()
    End Sub
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Do whatever you want to do with data if it isn't the last row.
    End Sub

    Open in new window

    LVL 3

    Author Comment

    Thanks for these solutions, i will trial each, however i think Jared_S suggested solution might work well,

    I think Valentino_V is good, unfortunately, at this point i don't need to process the records in the file, and the associated links are a little overkill (but very interesting reading!) for what i need here.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    This video discusses moving either the default database or any database to a new volume.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now