Miscellaneous Ramblings on Hacking

October 16, 2006

SSIS Script: RowCounter

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 10:06 pm

Straight from the MSDN archives, I found this little gem to count rows within a data pipe as they are processed. The key phrase is: “as they are processed”. You’d think the Row Count transformation would work if you wanted a sequential row number. Unfortunately, this transformation only updates the assigned variable at the end of processing for the given Data Flow task.

As a lover of code snippets for reuse, here’s what you have to do:

1. Add an Output Column.  I called mine rowCount.

2. Add  the following script lines:

Public Class ScriptMain
  Inherits UserComponent
  Dim iCount As Integer = 0
  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.rowCount = iCount
    iCount = iCount + 1
  End Sub
End Class

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: