locked
Access Derrived Column from OleDB Command RRS feed

  • Question

  • Hi friends,

    is it possible to access a Derrvied Column from an OLE-DB Command?
    I have to Update a Table with a join and i need from the source Table columns which have to be pivoted before i can use it in the update Command.

    Best regards,
    Basti

    Friday, November 6, 2015 10:39 AM

Answers

  • Hi Basti,

    According to your description, you want to pass a derived column as a parameter for the query in the OLE DB Command.

    To achieve your requirement, we can refer to the following steps:

    1. Connect to the database as the connection manager for OLE DB Command.
    2. In the Component Properties pane, write the query below in the SqlCommand property:
      UPDATE Table SET column = ?
    3. In the Command Mappings pane, select the Derived Column maps to the destination column Param_0. The following screenshot is for your reference:


    References:
    OLE DB Command Transformation
    Configure the OLE DB Command Transformation

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, November 9, 2015 6:41 AM

All replies

  • Yes. you can access derived column within OLEDB Command provided both are inside data flow task and OLEDB command comes after derived column.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, November 6, 2015 11:09 AM
  • OK, thanks. Than i think i have a Problem in my syntax.

    When i try to access like: UPDATE Table SET column = DerviedColumn it will not work.

    Is there a special type, how to access the derrived colum?

    I can see, that the coulmn is available in the input columns.

    Friday, November 6, 2015 11:30 AM
  • OK, thanks. Than i think i have a Problem in my syntax.

    When i try to access like: UPDATE Table SET column = DerviedColumn it will not work.

    Is there a special type, how to access the derrived colum?

    I can see, that the coulmn is available in the input columns.

    This question has already been answered:

    https://social.msdn.microsoft.com/Forums/en-US/c45e6693-8bad-464c-9279-0fe3389bc392/how-to-use-ole-db-command-to-update-a-derived-column?forum=sqlintegrationservices

    Shreepal

    Future-MVPian with a Motive to Help

    Please mark as answer/helpful, if it is useful

    Friday, November 6, 2015 11:33 AM
  • Hi Basti,

    According to your description, you want to pass a derived column as a parameter for the query in the OLE DB Command.

    To achieve your requirement, we can refer to the following steps:

    1. Connect to the database as the connection manager for OLE DB Command.
    2. In the Component Properties pane, write the query below in the SqlCommand property:
      UPDATE Table SET column = ?
    3. In the Command Mappings pane, select the Derived Column maps to the destination column Param_0. The following screenshot is for your reference:


    References:
    OLE DB Command Transformation
    Configure the OLE DB Command Transformation

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, November 9, 2015 6:41 AM