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.
LVL 3
adammet04Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
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()
            Input0_ProcessInputRow(Buffer)
        End While

        If Buffer.EndOfRowset Then
            Process the last row
       End If

End Sub

Public Overrides Sub CreateNewOutputRows()
        OutputBuffer.AddRow()
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

0
 
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/)
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.