modifying sqlcommand of data reader source that using oracle

Question

modifying sqlcommand of data reader source that using oracle

prayojana

Grasshopper

Points: 19
More actions
February 29, 2008 at 12:51 am

#128179

can anyone give hints of how to modify sqlcommand at runtime for datareader source that use ado.net Oracle ?
there is no 'expression' or sql command from variable so i have no idea of how to modify the sqlcommand ?
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jay1234 SSCommitted Points: 1624 More actions · Answer 1

I can't find any option to make sql dynamic in data reader. The option I can think of is to use OLE DB Source pass sql command from variable.

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 2

Jay (2/29/2008)
I can't find any option to make sql dynamic in data reader. The option I can think of is to use OLE DB Source pass sql command from variable.

I am having the same problem.

We are running in several modes Production/UAT/Development and need to dynamically configure schemas to drag information from. The Server connection stays the same.

So we need to modify the SQL statements from

SELECT * FROM prd.customers

into

SELECT * FROM uat.customers

etc.

As far as I can see it is impossible when using DataReader.

How do we use then OLE DB Source with Oracle?

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Jay1234 SSCommitted Points: 1624 More actions · Answer 3

Valek,

In your case create two variables 1)SqlStatement (string)

2)Schema (string).

For SqlStatement variable - in the properties - change "evaluate as an expression" to true and assign expression as

"SELECT * FROM " + @Schema+".customers"

Where @schema variable can be prd or uat.

@Schema variable can be controlled by a xml configuration file (For details about configuration file go to - http://msdn2.microsoft.com/en-us/library/ms141682.aspx)

In the OLEDB task of Data Flow - choose data access mode as SQL Statement from variable, in this case it would be SqlStament.

Let me know if you need more info.

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 4

Hey. Thank you for help.

I just figured out. If using OLEDB source you could source the SQL statement from a user variable.

When using a Datareader you could create an expression to modify the SQL statement, but it is not controlled on the element itself, instead it is controlled on the whole dataflow block, so you have to go out of the dataflow tab and fix the expressions outide in the properties of the dataflow block.

Anyways I fixed my problem, cheers guys

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM