locked
Variable Mapping in SSIS RRS feed

  • Question

  • Hi Guys.

    Is it possible to map in variables in data flow mappings?
    I have an excel that is mapped to an SQL server table, and the SQL server table actually contains an additional field which can be retrieved from a package variable.
    Any way I can map the variable to the table?

    Thanks.
    Tuesday, May 24, 2005 3:17 PM

Answers

  • Sure, you can introduce the Variable value into data flow by using a Derived Column transform. This will create a new column with your data which can be mapped to the column in the destination.

    Regards
    Tuesday, May 24, 2005 6:31 PM

All replies

  • Sure, you can introduce the Variable value into data flow by using a Derived Column transform. This will create a new column with your data which can be mapped to the column in the destination.

    Regards
    Tuesday, May 24, 2005 6:31 PM
  • How do I do this? I am having a SQL Command for the source query. I want load one of variable also to the destination table. How do I do this?
    Monday, October 17, 2005 7:13 PM
  •  mci wrote:
    How do I do this? I am having a SQL Command for the source query. I want load one of variable also to the destination table. How do I do this?


    In your data-flow you currently have (I would imagine) an Excel source and an OLE DB Destination. Remove the green data-path between them and then drag a Derived Column transform from the toolbox and place it in between the 2 existing components. Join them up using green data-paths.
    In the Derived Column transform editor drag the variable that you want from the window near the top left of the editor into the columns grid at the bottom. This will create a new column containing the value of the variable. From thereon you should be able to fiddle with it to get exactly what you want.

    -Jamie
    Tuesday, October 18, 2005 6:55 AM
  • What about having a variable in the SQL statement when you want to use the query in the OLE DB Source tool?

    For example, I want something like:

    SELECT co.firstname, co.lastname, cl.id, cl.offer FROM tblConsumer co INNER JOIN tblClickdata cl ON co.id = cl.id WHERE cl.id = [variable name]

    I can't place a Derived Column tool before the OLE DB Source.  Is there any way I can get this variable into the SQL statement in the OLE DB Source tool?
    Tuesday, October 18, 2005 7:09 PM
  •  John Jeffers wrote:
    What about having a variable in the SQL statement when you want to use the query in the OLE DB Source tool?

    For example, I want something like:

    SELECT co.firstname, co.lastname, cl.id, cl.offer FROM tblConsumer co INNER JOIN tblClickdata cl ON co.id = cl.id WHERE cl.id = [variable name]

    I can't place a Derived Column tool before the OLE DB Source.  Is there any way I can get this variable into the SQL statement in the OLE DB Source tool?

    Why not set the OLE DB Source to get the SQL statement from a variable? On the variable you can set EvaluateAsExpression=TRUE and set the expression to be the parameterised SQL statement.

    -Jamie
    Wednesday, October 19, 2005 8:15 AM
  • I was doing it the first way (SQL statement from a variable), but I'm really new to SSIS, no experience with DTS either, and I'm still trying to figure out the right way to do things.

    Thanks for the help!
    Wednesday, October 19, 2005 4:17 PM
  •  John Jeffers wrote:
    I was doing it the first way (SQL statement from a variable), but I'm really new to SSIS, no experience with DTS either, and I'm still trying to figure out the right way to do things.

    Thanks for the help!


    No problem John. Did it work for you?

    -Jamie
    Thursday, October 20, 2005 6:47 AM