Help Passing a variable using OLE DB source

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Help Passing a variable using OLE DB source

Help Passing a variable using OLE DB source

Rate Topic

Display Mode

Topic Options

Author

Message

hogpen

Posted Tuesday, March 05, 2013 9:26 PM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 28, Visits: 180

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

opc.three

Posted Tuesday, March 05, 2013 11:26 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 6,730, Visits: 11,782

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427181

hogpen

Posted Wednesday, March 06, 2013 6:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 28, Visits: 180

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.

Post #1427344

rhoskins

Posted Thursday, March 07, 2013 8:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 3:59 AM
Points: 6, Visits: 139

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! Smile

Post #1428032

« Prev Topic | Next Topic »

Permissions