January 26, 2009 at 3:46 pm
I have a sql script in an ExecSQL task.
The SQL is dynamic.
For example, this:
declare @sql as varchar(max)
declare @DB as varchar(50)
set @DB = @[User::strDB] --<<<< doesn't work
set @DB = ? --<<<doesn't work
set @sql =
'update mt
set mt.myCol = ht.hisCol
from
myTable mt inner join
' + @DB + '.dbo.hisTable ht on mt.myKey = ot.hisKey'
exec(sql)
How can I refer to the package var User::strDB in the ExecSQL task?
[font="Courier New"]ZenDada[/font]
January 27, 2009 at 6:09 am
Why are you using dynamic SQL in your T-SQL statement? If you are going to build a SQL statement with a 4 part name, you may as well just use the variable.
Rather than using the SQLSourceType of "Direct input" in your Execute SQL task, use "Variable". Make your variable and expression variable and make the expression something like:
[font="Courier New"]"update mt set mt.myCol = ht.hisCol
from myTable mt inner join
[" + @[User::strDB] + "].[dbo].[hisTable] ht on mt.myKey = ot.hisKey"[/font]
January 27, 2009 at 8:19 am
It was actually 421 lines of code. I went with a sproc and did as you said.
Thanks!
[font="Courier New"]ZenDada[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply