SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help Passing a variable using OLE DB source


Help Passing a variable using OLE DB source

Author
Message
hogpen
hogpen
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 323
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!
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36828 Visits: 14411
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
hogpen
hogpen
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 323
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.
rhoskins
rhoskins
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 236
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!:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search