OLE DB Source

Hi,

Im making a query in my Ole DB Source.

How can I use a variable in my query?

Thank you.

Make your query in an expression on another variable. Then inside that expression you can use whatever variables you desire.

Then, use the "query" variable as the SQL Source for the OLE DB Source.|||

Thank you Phil.

I know how to make a variable, go to properties of that variable and set to evaluate it as an expression.

I'm sorry to ask you this, but can you give me an example of how you fit a query inside an expression?

Thank you as always!!

|||

I tried to put the following inside my variable′s expression builder:

"select * from MyTable r
where r.id= " + (DT_WSTR,50)@.[MyVariable]

However, it doesn′t show up in my Ole DB Source Editor when I set Data Access Mode to "Sql command from variable".

Any ideas?

|||Expression:

"select column1, column2 from table where key = " + (DT_WSTR,50)@.[User::MyKeyVariable] + " and ..........."|||You'll want to use "SQL Command from variable" instead. Also, check the scope of the "query" variable you created. Likely, the scope of it is such that the data flow cannot see it.|||Oh, one other thing... Best practices would be to list out all of the columns you need (and ONLY those columns you need) in your query. That does two things:
1 - Prevents changes to the table from affecting the metadata of the package (column additions, etc...)
2 - Introduces only the data necessary for the data flow and hence reduces the overhead of processing the data.|||

Thank you for all this help!!

My Expression variable is in the scope bu it doesn′t show up in the DropDown list inside Ole DB Source :/

When I evaluate my expression I get:

select * from MyTable r where r.ID = 0

Does it maby matter "when" I set the value of this expression? I just created the expression variable out of the blue (not anywhere in my flow).

Any ideas what could be wrong?

Thank you again!

|||It can't be in scope. Double and triple check.

Any string variable of appropriate scope should show up in the list.|||

Strange my variable pops-up now

Thank you so much!!

|||

One last thing.

Don′t I have to make this expression inside some data flow when the variable that I use inside the expression has a value(else it wil be 0 always)?

Or is the expression evaluated at runtime when I use it in the OLE DB Source?

Thank you thousand times!!

|||The expression will be evaluated at runtime.|||

Dear Phil Brammer,

This worked! I can′t thank you enough!!

I'm really speechless, both for your help and also that this is possable

Yours,

MrHat