Tuesday, May 17, 2011

SSIS Selective Rows Count - Using Script Component

I came across a question today about how to process only the first 10000 rows from a flat file.
It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx .

or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task.

"Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number.

But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400".

Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue
I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx
just to generate 5000 rows, you can use a flat file as normal.


I'm here selecting only the first 1000 rows or whatever number, using a script task, not using any custom tasks.



Same concept, I'm using a Generator source to give me 5000 rows, which I'll filter using a conditional split.

Add a script component (Transformation) and add an output row (Integer)


 Edit the script, I'm using VB.net here.


Conditional split
You can here change the selection to be from row 500 to 750, you would use:
ORowNumber <= 750  && ORowNumber > 500


and the results.....