SQL Server Performance

SSIS Variable - simple - please advise

Discussion in 'SQL Server 2005 Integration Services' started by sql_jr, Jul 25, 2007.

  1. sql_jr New Member

    What is the best way to do the following (please give precise steps)

    I basically have a source query, that I want to parameterize (so we can run this against any environment)

    For ex: Source Query
    select * from MyServerVar.MyDBVar.dbo.Table.

    How can I do this using SSIS? More important is the db reference, but please advise for both. TIA!
  2. dineshasanka Moderator

    YOu can use a script task to generate entire query

    Create variable name sqlQuery,

    Pass MyServerVar,MyDBVar as ReadonlyVariable and sqLQuery as ReadWrite variable to the above script task.
    Inside the script task generate the entire sql query and assign it to sqlQuery

    Then for the data source select ,sql command from varialble and assign sqlQuery

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. sql_jr New Member

    Thanks for your reply!

    I already have some various data flows setup w/queries. Is there a way to just initialize the variable(s), and reference them somehow in the query? ie MyDBVar..table?
  4. dineshasanka Moderator

    It depends on the situatons.

    If you need this inside query, then you have to use script task

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  5. sql_jr New Member

    dineshasanka - tx for your help. I need to know based on your suggestion something specific. How/where would I put the query in the script task? Would this be part of the design script, and therefore this is vb? Sorry for my juniorism in this (sql_jr)
  6. dineshasanka Moderator

  7. hodgy New Member

    Another option would be to use variables to hold the database and server names, and use this in the connection manager. Then, your query would just be "select * from dbo.Table"
    You use a table to hold the meta data, which would allow you to dynamically loop through all your servers & databases that you want to and run the specified query.
    To do this:
    1. create two variables: strDatabaseName & strServerName
    2. create a connection using the connection manager, using any one of your servers and databases just to set it up
    3. Go to the properties of this connection, expand the "expressions" section and click the "build" button (3 dots)
    4. You will then be presented with a number of properties that can have variables assigned to them. Choose ServerName from the list of properties, then click the "build" button in the "expressions" box. This opens a new window called "Expression Builder".
    5. Expand the list of variables on the left, find the variable called "User::strServerName". Drag this down to the Expression window. You should see the following text "@[User::strServerName]". Click OK.
    6. Do the same for the InitialCatalog property using the strDatabaseName variable
    That's it. You can now change these variables either dynamically using a meta data loop, or at run time, or on an ad-hoc basis.
    I think that is a little simpler than using script tasks to build dynamic sql, plus it works across any tasks that use that connection. Allows for easier maintainance and reduces the need for hard coding.

    Hope this helps,
    Tom

Share This Page