(This blog post was first published on 20-jun-2013 on my former blog)

The problem

For one of my projects I needed to add the file line number to the staging table, so that the raw data that was inserted into the database, would be tracable back to the source files exact line.

First I wanted to use the RowCount component in the Data Flow Task, it was easy to configure (only assign a variable) but it appeared that the variable only got the value of the current row after the Data Flow Task had finished, so you knew afterwards how many rows had gone through the dataflow. But that was not what I needed! I want an instant row number, telling me which row is flowing through right now!

The solution: a script component

So what we have to do is add a script component to the Data Flow Task.

 

But remember, if we work with a ReadWrite variable, we will have the same problem as with the RowCount component: the value of the variable is not updated until the dataflow task has finished, so in the flow it will have the initial value for every row.

To work around this, we have to add an output column to the script component as follows:

 

Now we have to edit the script. No ReadOnlyVariables or ReadWriteVariables need to be set, just press the <Edit Script> button.

As the script really is just a C# class, all we have to do is add a private variable, initialize it, increase it for every row and assign the value to the outputcolumn “RowNumber”.

I will show you how:

Simply add the parts in red boxes to the script:

That’s it. Simple, hey?

Now, there is one more thing to do. Open your OLE DB Destination and make a mapping for the RowNumber column. In my example the database field is also called RowNumber, but this is of course not required, you can give it any name you like.

Now you’re done, run the script and you will see that the RowNumber column in the destination table will increase for every row that is inserted!