question

Deepak-9940 avatar image
0 Votes"
Deepak-9940 asked Deepak-9940 answered

Passing Variables to Oracle ODBC Source in SSIS 2019

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

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Deepak-9940 avatar image
1 Vote"
Deepak-9940 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered Deepak-9940 edited

Hi @Deepak-9940,

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zoehui-MSFT ,

Thank you for replying. Here's the query.

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

It's an Oracle Source Provider (ODBC) and does not contain any set Query Parameters dialog box.

Kind Regards,
Deepak

0 Votes 0 ·