Thursday, November 21, 2013

SSIS - Use Dynamic SQL in Oracle Source of Oracle Attunity connector

Recently I was working with Oracle Attunity source to pull the data from Oracle source. There was a requirement we need to pass a dynamic SQL to the source. But what I could see in the Oracle source only two options Table name & SQL Command. There is no option for sending SQL Command as variable.





But by the below way we can pass the dynamic SQL to Oracle source.1.Choose the data flow task in which the Oracle source is implemented.2.Go to properties and click on the Expressions.3.There you can see the properties [Oracle Source].[SQLCommand] .4.Set the expression for the above property for the dynamic SQL.




4 comments:

  1. Hi Sorna,
    How do you do the field Mappings within the Data Flow task (eg. from Oracle Source to ADO NET Destination) if the dynamic SQL points at different tables?

    ReplyDelete
  2. Hello Widey,

    If you talking about different column set itself , SSIS will not support the dynamic mapping at run time if the column metadata changes. For this you have create the packages dynamically using c# code or BIML scripts.

    ReplyDelete
  3. Hi Sorna, i am trying to make your steep but when i click right button to properties in Oracle Source i cant found de item Expresion to edit the Dinamic SQL i cant see, do you know if i make wrong or i need get install something or whay happen?
    i let u here a screnn for mi propierties of dataSouce Oracle.
    http://s2.subirimagenes.com/imagen/previo/thump_9259380propiedasdes.png

    ReplyDelete
  4. Hello Sorna, how are you doing? I hope you're fine.

    I found a SQL Script wrote by you and I'm having some problems that I'd like to understand. I apriciate if you could help me.

    The script is "Search for a string in all tables of SQL Server Database". I've got in the TechNet Gallery.

    After create that stored procedure the one that I have in my Database stop to work. I have one that do the same as yours but in all the tables and the option that yours has (select one or more tables is what I'm looking for).

    I notice that if I try to search for a string that starts with "<" the stored procedure do not work either.

    Could you help me on that?

    By the way thanks for share you codes.

    Regards

    Rogerio Picilli

    ReplyDelete