Post reply

Pass Database Variable Names in SQL Command

  • July 9, 2009 at 2:14 pm

    #223435

    Hi,

    I got a lot of sql scripts that joins tables from multiple databases and

    I need to be able to change these from time to time using the SQL command such as below:

    *******

    Select col1, col2 from varDBName1.dbo.table1 as x, varDBName2.dbo.table1 as y where

    x.id = y.id

    *******

    varDBName1 and varDBName2 are db variable names that I can modify at any time.

    Please let me know on how to achieve this in SSIS.

    Thanks,

    Christopher

  • July 9, 2009 at 2:32 pm

    #1022680

    You can use SQLCMD mode (Menu -> Query -> SQLCMD Mode) like this:

    :setvar DatabaseName "Sandbox"

    SELECT COUNT(*) FROM [$(DatabaseName)].dbo.Tally

    Edit: Dang! Sorry didn't notice the SSIS forum. Sorry!

  • July 9, 2009 at 3:01 pm

    #1022703

    Sorry but how do you set this in SSIS?

  • July 9, 2009 at 3:09 pm

    #1022709

    Sorry, I recognized that this is the "SSIS Forum" after I posted my answer...

    Since I'm primary a .NET developer I would use wild-cards within the scripts and a Script Task to replace them with current database name, but I'm quiet sure there are better ways to handle this in SSIS.

    Hope anybody else can provide a better solution.

  • July 9, 2009 at 4:27 pm

    #1022762

    Florian Reischl (7/9/2009)

    Sorry, I recognized that this is the "SSIS Forum" after I posted my answer...

    Since I'm primary a .NET developer I would use wild-cards within the scripts and a Script Task to replace them with current database name, but I'm quiet sure there are better ways to handle this in SSIS.

    Hope anybody else can provide a better solution.

    Florian,

    Unfortunately your solution is the only one available currently. There is no better one.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply