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:

Created a package variable
Set the variable to evaluate as expression true
Put the query in expression along with parameter as a different package variable
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')

I don't have an Oracle instance but if I understand the problem, the attunity driver doesn't allow for a "normal" parameterization. Therefore, the approach is to set the property at the data flow level for this source as an SSIS Variable. Your question then becomes, what query do you use inside the data flow? — billinkc
– billinkc, Commented Feb 18, 2014 at 4:25
thanks billinkc !! That is exactly what i meant... I have updated the question. — Prateek Singh
– Prateek Singh, Commented Feb 18, 2014 at 4:42
Create your data flow with your non parameter query. This lets the designer set the metadata. Once that's done, then add the expression to the data flow — billinkc
– billinkc, Commented Feb 18, 2014 at 4:47
Hi Please see my update, now i can get the metadata but not able to run the package due to error. — Prateek Singh
– Prateek Singh, Commented Feb 18, 2014 at 7:39

billinkc · Accepted Answer · 2014-02-19 02:52:25Z

7

+50

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')"

answered Feb 19, 2014 at 2:52

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

APC Over a year ago

"It is important that your SSIS Variable be set with Evaluate as Expression set to true" - yes indeed, this just tripped me up!

Brian MacKay · Accepted Answer · 2014-07-30 04:53:10Z

4

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

answered Jul 30, 2014 at 4:53

Brian MacKay

32.2k18 gold badges96 silver badges127 bronze badges

Collectives™ on Stack Overflow

SSIS - Using parameters in Oracle Query using Attunity Oracle Datasource

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related