+ Reply to Thread
Results 1 to 8 of 8

Passing SSIS [user] Variable to dynamic SQL COMMAND

  1. Passing SSIS [user] Variable to dynamic SQL COMMAND

    Hi,
    I have an SSIS user VariableName strTableName
    and I want to pass it into my dynamic SQL command:
    SELECT COUNT(*) FROM strTableName

    **this is a dynamic query.
    Can someone show me how to do this.

    Thank You

  2. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    "New to SSIS" wrote in message
    news:33ada177-a799-43a9-b99e-666a8ca0888d@u10g2000prn.googlegroups.com...

    > I have an SSIS user VariableName strTableName
    > and I want to pass it into my dynamic SQL command:
    > SELECT COUNT(*) FROM strTableName
    >
    > **this is a dynamic query.
    > Can someone show me how to do this.


    Use an expression. Select your Execute SQL Task and click the ... button by
    Expressions in the property grid. This opens up a dialog. In the left cell
    of the grid, drop down the list and select the SqlStatementSource (or
    whatever it's called--I'm doing this off the top of my head) property. The
    in the right cell click the ... button. This opens the expression builder
    dialog. In the text box at the bottom, type (exactly as shown):

    "SELECT COUNT(*) FROM [" +

    Now expand the tree in the upper left and find your variable name. Drag it
    into the box after the + sign. The box should now contain the following
    text:

    "SELECT COUNT(*) FROM [" + @[User::strTableName]

    Add text to the end so that the box contains this:

    "SELECT COUNT(*) FROM [" + @[User::strTableName] + "]"

    Then click OK however many times you need to. Boom! You're done. (For
    reference, you could have simply typed the text exactly as I did, but it's
    nice to know how the UI elements work.)

    Did you notice how I added [ ] around the table name? ALWAYS a good idea
    when dealing with variable text.

    One final piece of advice: drop the Hungarian notation from your variable
    names. Call it TableName, not strTableName. And this is coming from a VB6
    guy. I've abused Hungarian for years. In the .NET world, its time is
    ended....



  3. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    I was being able to follow your instruction and typed in the
    expression. However in the "Property Expression Editiors' box, there
    is a property drop down and the expression, I don't know which
    property to select. I tried a couple but unsucessful.

    In addition, in the 'Data flow task' where you select OLE DB source
    editior, you can chose 'SQL COMMAND from variable' and use user
    variable.
    I prefer this way, but don't don't how to pass the value from one
    variable to another

    this is what Iwant to do.
    I have two user variables
    1. tblName string
    2.strStatement string

    I want to concat strStatment as: "SELEC * FROM ' + tblName

    Thanks

  4. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    "New to SSIS" wrote in message
    news:27f86fec-050d-493d-86eb-013d8ca51545@c19g2000prf.googlegroups.com...

    >I was being able to follow your instruction and typed in the
    > expression. However in the "Property Expression Editiors' box, there
    > is a property drop down and the expression, I don't know which
    > property to select. I tried a couple but unsucessful.


    I told you: SqlStatementSource (or something very similar).

    > In addition, in the 'Data flow task' where you select OLE DB source
    > editior, you can chose 'SQL COMMAND from variable' and use user
    > variable.
    > I prefer this way, but don't don't how to pass the value from one
    > variable to another


    You can set the EvaluateAsExpression (again, or something like that)
    property to True and then use

    "SELECT * FROM [" + @[User::strTableName] + "]"

    as the Value. I can't stress how important it is that you surround the table
    name with brackets. I'm sure you think strTableName will NEVER contain a
    space, but one day it will and you'll be wondering why your SQL statement
    suddenly started failing. With the brackets, it'll be fine forever.



  5. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    "Jeff Johnson" wrote in message
    news:As2dnTXHlv8Qi2PanZ2dnUVZ_hmtnZ2d@datapex...

    > You can set the EvaluateAsExpression (again, or something like that)
    > property to True


    That was not as clear as it should have been. This is a property of the
    variable itself, so select the strTableName variable in the Variables window
    and then set the properties I mentioned.



  6. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    On Apr 10, 6:30*am, "Jeff Johnson" wrote:
    > "New to SSIS" wrote in messagenews:27f86fec-050d-493d-86eb-013d8ca51545@c19g2000prf.googlegroups.com...
    >
    > >I was being able to follow your instruction and typed in the
    > > expression. However in the "Property Expression Editiors' box, there
    > > is a property drop down and the expression, I don't know which
    > > property to select. I tried a couple but unsucessful.

    >
    > I told you: (or something very similar).
    >
    > > In addition, in the 'Data flow task' where you select OLE DB source
    > > editior, you can chose 'SQL COMMAND *from variable' and use user
    > > variable.
    > > I prefer this way, but don't don't how to pass the value from one
    > > variable to another

    >
    > You can set the EvaluateAsExpression (again, or something like that)
    > property to True and then use
    >
    > "SELECT * FROM [" + @[User::strTableName] + "]"
    >
    > as the Value. I can't stress how important it is that you surround the table
    > name with brackets. I'm sure you think strTableName will NEVER contain a
    > space, but one day it will and you'll be wondering why your SQL statement
    > suddenly started failing. With the brackets, it'll be fine forever.


    I did set the evaluateasexpression = true
    and used SqlStatementSource for property but still can't evaluete my
    expression.

  7. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    On Apr 10, 8:55*am, "Jeff Johnson" wrote:
    > "Jeff Johnson" wrote in message
    >
    > news:As2dnTXHlv8Qi2PanZ2dnUVZ_hmtnZ2d@datapex...
    >
    > > You can set the EvaluateAsExpression (again, or something like that)
    > > property to True

    >
    > That was not as clear as it should have been. This is a property of the
    > variable itself, so select the strTableName variable in the Variables window
    > and then set the properties I mentioned.


    Found many samples including your suggestion but non is working or I
    couldn't follow insturction.

  8. Re: Passing SSIS [user] Variable to dynamic SQL COMMAND

    I see the problem.
    I couldn't tell the difference between expression and value.
    I must enter the expression of the value not the value itself.

    expression "SELECT * FROM Mytable WHERE COL1 = " +
    @[User::strTableName]
    the value is SELECT * FROM Mytable WHERE COL1 =
    the value is the result of the expression.

    Hope this help those who ran into the same issue.

+ Reply to Thread