SSIS OleDB Destination Sql Command From Variable

  • Hi all,

    I need to put a dynamic query to OleDB destination SQL command (Data access mode)

    But I don't find any option for that.

    Lets say I have my query in the variable [user::variable1].

    How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

  • er.mayankshukla (2/24/2015)


    Hi all,

    I need to put a dynamic query to OleDB destination SQL command (Data access mode)

    But I don't find any option for that.

    Lets say I have my query in the variable [user::variable1].

    How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

    Can't be done, as far as I know. This is set at design time.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Did you mean OLEDB Command, by the way?

    OLEDB Destination does not have a SQLCommand.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (2/24/2015)


    Did you mean OLEDB Command, by the way?

    OLEDB Destination does not have a SQLCommand.

    Well, it does have one, but I don't think it's actually used. (or actually useful)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Silly me. I did not edit the source XML to check 🙂

    To save others the bother, here is what it looks like (SSDT 2013):

    <property

    dataType="System.String"

    description="The SQL command to be executed."

    name="SqlCommand"

    UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor"></property>

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • hmm,

    Looks like I have to use hardcodes table names in sql command in OleDB Destination

  • er.mayankshukla (2/24/2015)


    hmm,

    Looks like I have to use hardcodes table names in sql command in OleDB Destination

    I'm curious, how exactly do you use a SQL statement in the OLE DB Destination?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • er.mayankshukla (2/24/2015)


    hmm,

    Looks like I have to use hardcodes table names in sql command in OleDB Destination

    The table name in the OLEDB Destination component is a property.

    I can't see what SQLCommand has to do with anything: please explain.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I was planning to use the temp table as Destination.

    But to preserve the metadata while mapping I was thinking of using this expression in SqlCommand Data access mode of OleDB Destination

    "IF OBJECT_ID('tempdb..#cache_tbl') IS NULL

    begin

    select * from 'cache.tbl'

    end

    else

    begin

    select * form #cache_tbl'

    end

    "

  • That's probably not going to work.

    Your temp table has to exist when you are designing the package.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • er.mayankshukla - Tuesday, February 24, 2015 4:19 AM

    Hi all,I need to put a dynamic query to OleDB destination SQL command (Data access mode)But I don't find any option for that.Lets say I have my query in the variable [user::variable1].How can I assign this to SQL Command in OleDB destination, as I can only see hardcoding the sql query there ?

    Check this 
    SSIS Dynamic SQL Command

Viewing 11 posts - 1 through 10 (of 10 total)

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