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....

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

"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.

"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.

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.

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.

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.