Post reply

Need to use [User::MyVar] in ExecSQL task WITHOUT ?

  • January 26, 2009 at 3:46 pm

    #200515

    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

    #933120

    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

    #933225

    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