Microsoft Connector for Oracle - using ssis parameters

Question

Hi guys,

I've been using the new Microsoft Connector for Oracle to extract data from a Oracle database. I will need to create some logic to implement an incremental load based on 2 datetime fields of the source table.

I will need however to pass 2 datetime parameters (start_date and end_date) to the SQL query and I'm struggling with the syntax. Last time I did this, I used SQL Query from Variable but it's not available on the Oracle Source editor.

The date variables on SSIS are fed by a Execute SQL Task, I just need to know how to embed the variables in the SQL query that extracts data from the Oracle Source connector.

Thank you

Answer 1

Hi @Pedro Soares ,

To work around this, we can use [Oracle Source].[SqlCommand] property expressions of Data Flow Task. For more details, please see:

1. Created a package variable.
2. Set the variable to evaluate as expression true.
3. Put the query in expression along with vaiable v_num as a different package variable.
4. Open the Data Flow Task properties window.
5. Click on the "..." beside the "Expressions". That will open the Property Expressions Editor.
6. For the Property, select the [Oracle Source].[SqlCommand].
7. For the Expression, click the "...", this will open the Expression Builder window in which you can expand the Variables node and drag and drop the variable you want.

Here is a nice article you may take a reference to.

how-to-parameterize-source-query-attunity-oracle-connector

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Great, it's working. Thank you

As an additional note, just to blast off some steam, it would be great if the parameter mapping and result set could be changed. I find this mechanism very obsolete and not adequate for today challenges, It puzzles me that Microsoft over 15 years didn't change practically nothing on SSIS components and we have a text editor that we fill with ? and then on another windows we order those parameters. Not only it's not practical, it is a very poorly designed UI for an ETL tool.