[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1227
  • Last Modified:

SSIS - Get last line in a flat file

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.
1 Solution
ValentinoVBI ConsultantCommented:
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: http://www.sqlis.com/sqlis/post/Handling-different-row-types-in-the-same-file.aspx

If the last row cannot be distinguished, have a look at this method: http://microsoft-ssis.blogspot.com/2011/02/how-to-skip-trailer-records.html
(found through http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8454da9c-4ed1-4020-bcae-d00e60a8f1b9/)
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

adammet04Author Commented:
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.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now