SSIS - Read Top X Rows From Flat File / Excel File OR Range OF Rows

Scenario:

We have Flat File/Excel File as our source and we have to load only top 10 rows into out destination table Or the requirement can be load the records from Row number 5 to 10.

Solution :

Here is our solution, We will be reading the flat file by using Flat File Source, after that we will use Script component as transformation to generate the sequence number for each of the record. Once we have the sequence number we will be using Conditional Split to get top X records or range of rows depending upon our requirement.

Step 1:

Here is sample data that I am using for this sample package. I have 25 records. Please create sample file on your desktop or any drive you want.

Bring Script Component and connect your Flat File Source to Script Component, When you will bring Script Component to Data Flow Pane it will prompt you to Choose if you want to use Script Component as Source, Destination or Transformation,Choose Transformation.

After that configure as shown below

Choose the columns you want to Pass through

Step 4:

Add a new output column Seqno as shown below

Step 5:

Click on Script in Script Transformation Editor and Then Edit Script and paste below code or write what is shown in Red

Bring Conditional Split Transformation and connect Script Component to it and write expressions to get only top 10 rows. You can also defined range if you your requirement is let's see read records from 5 to 15.

Final Output:

Let's execute our package and see the output , It should only display top 10 records. I used Multicast and Data Viewer just to display the records. In real time scenario you will be inserting these records to some destination table/file.