March 5, 2013 at 9:26 pm
I'm having trouble passing information from a variable to a query. I currently have 3 variable for this: _QueryVariable, _ExcludeInfo, _ExcludeInfoString.
The main part of my query that I assume is causing the issue is:
AND MTR_NO||MTR_SFX NOT IN ("+ @[_ExcludeInfoString] +")
I receive an ORA-00936 Error: missing expression.
My OLE DB Source Editor uses SQL command from variable for the _QueryVariable and the above statement is used as part of an expression in the variable pane. The _ExcludeInfo variable is populated prior using an execute sql task. It is then converted to a string through a VB script and the OLE DB step is next. I've seen online people use AND MTR_NO||MTR_SFX NOT IN (?) instead but I can't seem to get that to work and don't know how the provider knows what variable to evaluate as the "?"
Any help would be greatly appreciated!
March 5, 2013 at 11:26 pm
Which Oracle driver are you using? Are you sure it supports OLE DB parameter mapping?
A simpler option may be to build your SQL Statement using an Expression in a Variable, and then just mapping the Variable to the SQLStatementSource property of the Execute SQL Task.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 6, 2013 at 6:29 am
I'm not sure to be honest. I looked up something on my computer that says oracle.odac.server 11.2.0.3 if that means anything. :ermm:
I'm pretty sure I have to use the variable in the OLE DB source editor based on how I have things set up to compare other information from multiple OLE DB source editors. I currently have the the select statement built using an expression in a variable.
I'm a little lost.
March 7, 2013 at 8:41 am
Forgive me if I'm not understanding your issue correctly, I'm new to this lark! I acheived something similar recently I think:
I passed a series of values generated via a SQL task as a result set to a variable with a datatype 'object'. I then used a foreach Loop container (Foreach ADO Enumerator) with a dataflow which queried an Oracle Database. Each value in the resultset variable was passed to a second variable (datatype string) via the loop. This in turn was passed as a parameter in the dataflow and rows inserted into (in this instance) an excel destination.
I used the Microsoft OLE DB\Microsoft OLE DB Provider for Oracle to connect to the oracle database in the dataflow using the SQL command from variable access mode and a third variable to store the query as an expression. The format of the Query Variable I used is as follows:
"SELECT column1, column2
FROM Table1
WHERE (Column3 = '" + @[User::SecondVariable] +"')"
All works nicely as I am just using it to match data that exists in a sql database with data from the oracle database and the volume is small.
Anyway - hope it helps, if not - ah well!:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply