Select top n records from source on data load

[josh.guffey]

I am loading data using this setup ODBC Enterprise --> Transformer --> SQL Server Enterprise.

I am trying to select the top 500 rows from my source to load my Development Environment. I know this can be accomplished using the Sequential File Stage via the Read First Rows option, but I am reading from an Oracle Database and cannot use the Sequential File Stage. I thought I could create a local parameter and just assign it the value of TOP 500 * and use inside of a user-defined SQL statement, but the job aborts every time. I learned that the only thing that can be passed as a parameter is an *. Does anyone have any suggestions on how to get the ODBC Enterprise Stage to read the first n records from a file?

Source: Oracle
Target: Sql Server

Thanks,

Josh

[chulett]

You should be able to handle this using normal (non-user-defined) sql and parameterizing the 'n' for the 'Top N'. Perhaps you can post your sql for our perusal?

[josh.guffey]

I figured it out. I am so used to working on SQL Server I forgot that Oracle has the nice feature ROWNUM. DUH!!!

In the user defined SQL, all I have to do is:

SELECT * FROM #ORACLE_DB_SRC_TBL# WHERE ROWNUM <= 500

Works like a charm.

Sorry for the post.

Thanks,
Josh

[chulett]

There you go.

Was going to suggest using "rownum" and parameterizing the "500" part in case it needed to change.