Post reply

How to use SQL variable in oracle provider for OLEDB in OLEDB data sourse

  • April 24, 2008 at 10:00 pm

    #123462

    I have to extract the data from Oracle data base..

    In Oledb data sourse i have connected to the oracle database.

    data access mode i have given ::SQL command

    my sql command was like this::

    select sol_id from kan(oracle table name) where sol_id='(variable created by me)'.

    when i wrun this querty it was giving an error..

    plz help me..

    Thanks

    koti

  • April 25, 2008 at 3:14 am

    #807018

    I'd be interested to know if this has changed at all in SQL 2008, however when we needed to pass variables in our source queries against an Oracle instance in our SSIS packages, we had to pass the SQL as a string variable as opposed to a SQL command.

    The steps were as follows.

    Create a new variable called Var1

    In the properties pane for Var1 set EvaluateAsExpression=TRUE

    Make the expression something like "SELECT * FROM YourTable WHERE YourColumn = " + @[Var2]

    Where @[Var2] is your parameter value, populated at runtime by a method of your choosing

    Now, in your OLE DB Source component, open up the editor and set Data Access Mode to be SQL Command from variable and set the VariableName to be Var1

    Kind Regards, Will

  • April 25, 2008 at 10:35 pm

    #807483

    I did according to your guidance ..

    It was giving the error like this:

    The data type of the variable(here my sql variable was in object data type ) was not supported in the expression..

    plz help me.

Viewing 3 posts - 1 through 2 (of 2 total)

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