Load Ingest Data
Results 1 to 7 of 7

Thread: SqlCommand from variable?

  1. #1
    Jim
    Jim is offline Junior Member
    Join Date
    May 2010
    Posts
    1
    Rep Power
    0

    Question SqlCommand from variable?

    I'm running queries against Oracle using SSIS in a For Loop container - the queries are about 24000 bytes long.

    Is there a way to specify a Variable as the Query Source (SqlCommand) for the Attunity Oracle Connectors?

  2. #2
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Jim,

    You can use property exprssions to specify a value for SqlCommand that is stored in a variable. For more information on how to use property expressions see:
    Using Property Expressions in Packages


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    pcand01 is offline Junior Member
    Join Date
    Jul 2010
    Posts
    2
    Rep Power
    0

    Need Variable as SQL Source

    Quote Originally Posted by Gadi.Farhat View Post
    Hi Jim,
    You can use property exprssions to specify a value for SqlCommand
    Hello, The problem is as follow. A variable is not limited in size but an expression is limited to a maximum of 4000 chars.
    You cannot set your variable value via expressions when the result exceed 4000 char but you can still set your variable value by other mean, i.e. an execute SQL task.
    It is frequent to have queries that goes over the 4000 char limit and that needs to be build dynamically.
    I cannot use Attunity Oracle connector for this. I am forced to use an OLEDB connector.
    It would be great if Attunity add the option of setting the SQL Command via a variable. This would allow to use the Attunity connector for these big queries.

  4. #4
    bbeliaso is offline Junior Member
    Join Date
    Oct 2010
    Posts
    4
    Rep Power
    0

    SQL Command from Variable

    I'm wondering if anyone knows of demand for this functionality? We, too, would be using these connectors if the "SQl Command from Variable" functionality was available in Attunity as it is for the stock connectors. Any ideas on when this might be released?

  5. #5
    Eduardo is offline Junior Member
    Join Date
    Feb 2011
    Posts
    2
    Rep Power
    0
    I'm having the same kind of problem. I need to change the query string at run time, so I use a variable to do it.
    This is the scenario and my approaches.

    - Source - Oracle 10G x64 Database
    - Destination Sql Server 2008 x64
    - Sql Server Integration Services 2008


    For example, using an user variable named SQL with value SELECT field1, field2, field3 FROM tablename
    - Setting table name for the AcessMode property and the variable @[User::SQL] for the TableName property.
    or
    - Setting SQL Command for the AcessMode property and the variable @[User::SQL] for the SqlCommand property.

    None of these works.

    Does anyone have any other way of doing this, some workaround?

    With an OLEDB connector we can use SQl command from variable as the AccessMode, and use an user variable without any problem, but I can't find a way to get the same behaviour using the Microsoft SSIS Oracle Connector.
    If this is not possible I find it to be a major flaw.

  6. #6
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Eduardo,

    As i mention before, you need to use property expressions and the property expression can be set to the variable you mentioned.



    In your case, you need to:
    1. Open the Data Flow Task properties window.
    2. Click on the "..." beside the "Expressions". That will open the Property Expressions Editor. See Attachment 184
    3. For the Property, select the [Oracle Source].[TableName] or the [Oracle Source].[SqlCommand].
    4. For the Expression, set your variable as the value (@[User::SQL]) or click the "...", this will open the Expression Builder window in which you can expand the Variables node and drag and drop the variable you want.
    5. Click OK.
    6. Save the package.

    Hope this helps...



    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    Eduardo is offline Junior Member
    Join Date
    Feb 2011
    Posts
    2
    Rep Power
    0
    Thanks, this was most helpful.
    I was trying to use the connector expressions, not Data Flow Task expressions.
    Too many time using OLE DB Connectors ... :o

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
  •