How do I limit the rowcount in an SSIS data flow task?

Question

I have an Oracle source, and I'm getting the entire table, and it is being copied to a SQL Server 2008 table that looks the same. Just for testing, I would like to only get a subset of the table.

In the old DTS packages, under Options on the data transform, I could set a first and last record number, and it would only get that many records.

If I were doing a query, I could change it to a select top 5000 or set rowcount 5000 at the top (maybe? This is an Oracle source). But I'm grabbing the entire table.

How do I limit the rowcount when selecting an Oracle table?

Answer 1

Hi

We can use the rowcount component in the dataflow and after the component make User::rowCount <= 500 in the precedence constraint condition while loding into the target. Whenever the count >500 the process stops to inserts the data into the target table.

thanks

prav

Answer 2

It's been a while since I've touched pl/sql, but I would think that you could simply put a where condition of "rownum <= n" where n = the number of rows that you want for your sample. ROWNUM is a pseudo-column that exists on each Oracle table . . . it's a handy feature for problems like this (it's equivalent to t-sql's row_number() function without the ability to partition and sort (I think). This would keep you from having to bring in the whole table into memory:

select col1, col2
from tableA
where rownum <= 10;

For future reference (and only because I've been working with it lately), DB2's equivalent for this is the clause "fetch first n only" at the end of the statement:

select col1, col2
from tableA
fetch first 10 only;

Hope I've not been too off base.