SSIS - Using parameters in Oracle Query using Attunity Oracle Datasource

Question

I am using the Attunity Oracle connector in SSIS to connect to a remote Oracle Server.

In my SSIS package, I need to connect to an Oracle database to fetch the data based on datetime parameters.

I followed the suggestions here to write a SELECT query with a parameter:

1. Created a package variable
2. Set the variable to evaluate as expression true
3. Put the query in expression along with parameter as a different package variable
4. Set the expression for [Oracle Source].[SqlCommand] at Data Flow to package variable (containing the query as expression)

I am good up to here, but if you are setting an expression for [Oracle Source].[SqlCommand] at the Data Flow, then what Query do I set in the "Oracle Source" inside the Data Flow task? How do I get the output columns and perform transformations?

I am not able to execute the package until I set a valid Oracle data source.

Every recommendation says to set the [Oracle Source].[SqlCommand] property at the Data Flow, but nobody mentions how to configure the Oracle source. Am I missing something here?

---

Update (2014/02/18) -

Based on comments by @billinkc, I created the data source with non-parameter query and added the expression at the data flow. When I execute the package, the query inside the data source changed to whatever is there in my package variable expression but it throws an error:

OCI error encountered. ORA-00936: missing expression

Here is my WHERE clause of the query, with the variable timestamp -

Where SL.RECEIVED_DATE = TO_DATE( @[User::Last_Run_Timestamp] , 'dd/mon/yyyy HH24:MI:SS')

Answer 1

To parameterize with Attunity Oracle data source, you need to get your metadata set first. This is usually done by just using the unparameterized query as the source. Then, in the Control Flow, on the Data Flow's Expressions you will sub in the SSIS Variable as a source.

It is important that your SSIS Variable be set with Evaluate as Expression set to true and then your formula must be created correctly. Unlike a PowerShell, the tokens are not replaced within in a string. Instead, you'll need to use classic string concatenation techniques. The following demonstrates casting the Variable @[User::Last_Run_Timestamp] to a string which allows me to concatenate, via +, with the rest of my filter.

"SELECT * FROM Table SL Where SL.RECEIVED_DATE = TO_DATE( " 
+ (DT_WSTR, 24)@[User::Last_Run_Timestamp] 
+ " , 'dd/mon/yyyy HH24:MI:SS')"

Answer 2

I just had to deal with this one. This is not very intuitive, but follow along...

• On the Control Flow designer, right click on the Data Flow itself and open Properties.

• Find 'Expressions' and click the ellipse to open the Expression Editor.

• Under property, select [Oracle Source].[SqlCommand] and then you can build an expression.

More details: http://sornanara.blogspot.com/2013/11/ssis-use-dynamic-sql-in-oracle-source.html