5

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

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Prateek Singh
  • 863
  • 1
  • 8
  • 28
  • Care to leave a comment Mr Down Voter?? – Prateek Singh Feb 18 '14 at 00:24
  • 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 Feb 18 '14 at 04:25
  • thanks billinkc !! That is exactly what i meant... I have updated the question. – Prateek Singh Feb 18 '14 at 04: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 Feb 18 '14 at 04:47
  • Hi Please see my update, now i can get the metadata but not able to run the package due to error. – Prateek Singh Feb 18 '14 at 07:39
  • Is that literally your where clause? In an SSIS Expression, you'd it looking like `"Where SL.RECEIVED_DATE = TO_DATE( " + (DT_WSTR, 24)@[User::Last_Run_Timestamp] + " , 'dd/mon/yyyy HH24:MI:SS')"` It won't see the variable tokens in there and replace them at runtime. – billinkc Feb 18 '14 at 12:59
  • Hey thanks man !! that did it :) Would you be kind enough to put this answer so that i can award the bounty to you sir.. – Prateek Singh Feb 19 '14 at 02:45

2 Answers2

7

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')"
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • *"It is important that your SSIS Variable be set with Evaluate as Expression set to true"* - yes indeed, this just tripped me up! – APC Jul 15 '19 at 12:07
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

Brian MacKay
  • 31,133
  • 17
  • 86
  • 125