Passing Variables to Oracle ODBC Source in SSIS 2019

Question

Hi All,

I have an SSIS package developed in VS 2019. It has an ODBC Oracle source with millions of records in a table. It transfers data to a SQL Destination. I'm trying to transfer 1 million record at a time by dynamically generating the SQL to use ROWNUM feature. My Source query looks something like this,

"SELECT * FROM (SELECT a.id, a. a.fld , a.mod_user_id, a.fld_vl_ext, ROWNUM r FROM adm.tusrvl a) WHERE r >" + @[User::intRCountStart] + " and r < " + @[User::intRCountEnd]

When I hit the preview button, I get the following error,

SQLSTATE: HY000, Message: [Microsoft][ODBC Oracle Wire Protocol driver]General error. setStmtType failed;

It this the right way of passing variables to Source query? Has anyone tried this and seen this error?

Environment:
Windows 10, Visual Studio 2019

Kind Regards,
Deepak

Answer 1

Hi All,

I came across a very nice article written by Melissa Coates and it helped my in solving the issue I was facing. Here's the link if someone has similar issue,

https://www.sqlchick.com/entries/2016/3/13/how-to-parameterize-source-query-attunity-oracle-connector

Kind Regards,
Deepak

Answer 2

Hi @Deepak ,

Have you mapped query parameters to variables in a data flow component?

map-query-parameters-to-variables-in-a-data-flow-component

You may also refer pass-a-variable-pararmeter-to-an-ssis-data-source-query-sql and how-to-pass-a-variable-for-a-sql-query-in-oledb-source for details.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October