Dynamic SQL in SSIS with Oracle

Hi ,

1.Dynamic Sql

-

My source table is Oracle .we want to have dynamic query the following steps we have done.

a.Created a new variable called StrSQL & ProductID variable contains values for where clause.
b.Set EvaluateAsExpression=TRUE
c.Set Expression=""select * from prod where product_id = " + @.[ProductID]
d.OLE DB Source component, opening up the editor
e.Set Data Access Mode="SQL Command from variable"
f.Set VariableName = "StrSQL"

But i am getting the following error

Error at DataFlow Task[OLEDB Source[1]]:An OLEDB error has occured, Error code: 0x80040e14.
AN OLE DB records is available . Source "Microsoft OLEDB Provider for Oracle" Hresult: 0x80040e14
Description : "ORA-00936:missing expression

What could be the problme.how about the support of dynamic query (Oracle) in ssis?

2.Clarification in Lookup.

--

How to Pass Parameters to Lookup. (Dynamic sql in Lookup)

example

select * from mastertable where reportdate = ?

here also my source is oracle table

Thanks

Jegan

The fact that the source is Oracle is irrelevant as far as the dynamic SQL is concerned. You should take the result of your expression and try and execute it against the source in yur query tool of choice (i.e. outside of SSIS). Verify that the query is correct.

-Jamie

|||

In c. of your question; you have a duplicate quotes

c.Set Expression=""select * from prod where product_id = " + @.[ProductID]

It may be that the problem or is just a typo in your post?

Other than that I don't see any other reason for the error; the steps you described look right.

Regarding the lookup question; I will not recommend that approach. If you have a dynamic query in a lookup it would mean to perform the lookup query for every row in your data flow impacting performance. However, I think you could do it by going to the advanced tab of the lookup transform and editing the query there.

Alternatively, you could include reportdate as a column available to the input of your lookup; then you just use it as a part of the join in the lookup component. Or may be you can create a view at run time with that query and then use that view in the lookup.

Those are just a couple of ideas; I hope this takes you further with a solution to your issues

Rafael Salas

|||

Rafael Salas,

Thanks for the Lookup solution.

Regarding dynamic sql yes its a typo error in my post. the same steps are working when we use a sql server connectivity.but for oracle i am not able to see the available columns in the column tab .what could be the problem any property need to be set?.

Thanks

Jegan.T