Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help Passing a variable using OLE DB source Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 9:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:48 AM
Points: 35, 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!

Post #1427158
Posted Tuesday, March 5, 2013 11:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:33 AM
Points: 7,107, Visits: 12,660
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
Post #1427181
Posted Wednesday, March 6, 2013 6:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 10:48 AM
Points: 35, 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.

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.
Post #1427344
Posted Thursday, March 7, 2013 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:03 AM
Points: 6, Visits: 164
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!
Post #1428032
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse