Load Ingest Data
Results 1 to 4 of 4

Thread: SSIS - Parameters in Oracle Query using Attunity Oracle Datasource

  1. #1
    pratsing1985 is offline Junior Member
    Join Date
    Feb 2014
    Posts
    5
    Rep Power
    0

    SSIS - Parameters in Oracle Query using Attunity Oracle Datasource

    Hi, I am using Attunity Oracle connector in SSIS to connect to a remote Oracle Server.

    In my ssis package, i need to connect to oracle database to fetch the data based on datetime parameters.
    I followed as suggested in here - http://www.attunity.com/forums/micro...eter-1397.html

    1. Created a package variable
    2. Set the variable to evaluate as expression true
    3. Put the query in expression along with parameter as a different package variable
    4. Set the expression for [Oracle Source].[SqlCommand] at Data Flow to package variable (containing the query as expression)

    I am all good until here, but if you are setting expression for [Oracle Source].[SqlCommand] at Data Flow, then what to set in "Oracle Source" inside the Data Flow task?
    How do i get the output columns and perform transformations?

    I am not able to execute the package until i set Oracle datasource properly.

    Every where ppl suggesting to set [Oracle Source].[SqlCommand] at Data Flow, but nobody mentions about Oracle source.
    Am i missing something here?

    Update (2014/02/18) -

    First I created the data source with non-parameter query and obtain the metadata and then added the expression at the data flow. When i executed the package, query inside the data source changed to whatever is there in my package variable expression (Which i wanted) but it throws an error when run the package -
    OCI error encountered. ORA-00936: missing expression

    Here is my WHERE clause of the query, with the variable timestamp -

    Where SL.RECEIVED_DATE = TO_DATE( @[User::Last_Run_Timestamp] , 'dd/mon/yyyy HH24:MI:SS')

    Thanks for your help....


    Thanks,
    Prateek
    Last edited by pratsing1985; 02-18-2014 at 03:43 AM.

  2. #2
    pratsing1985 is offline Junior Member
    Join Date
    Feb 2014
    Posts
    5
    Rep Power
    0

    No answers??

    Hi,

    Its been two days i have posted this question and i haven't got a single reply from Attunity Team on this.

  3. #3
    JasonSTX is offline Junior Member
    Join Date
    Aug 2012
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by pratsing1985 View Post
    Hi,

    Its been two days i have posted this question and i haven't got a single reply from Attunity Team on this.
    It does not matter what you have inside the actual oracle source as when the expression for sqlquery gets initialized, it overwrites whatever you had in the query. Traditionally, I evaluate my expression and take that output and paste it into the SQL command in the oracle source so that I have the correct metadata pulled down during development.

  4. #4
    srajaraman is offline Junior Member
    Join Date
    Mar 2014
    Posts
    1
    Rep Power
    0

    SSIS Oracle Attunity- stored proc via variable

    Quote Originally Posted by JasonSTX View Post
    It does not matter what you have inside the actual oracle source as when the expression for sqlquery gets initialized, it overwrites whatever you had in the query. Traditionally, I evaluate my expression and take that output and paste it into the SQL command in the oracle source so that I have the correct metadata pulled down during development.
    I tried doing the above, but I am still not getting the stored procedure to be executed via the variable. I am having an Oracle source and setting the data flow task's expression to the Oracle source sql command and value to the variable name. It is not executing the stored proc. PLease let me know what I am missing.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •