SSIS Variable – simple – please advise | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SSIS Variable – simple – please advise

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!
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/
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?
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/
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)
Yes it is vb.net
http://www.tutorialized.com/tutoria…in-SQL-Server-2005-Integration-Services/13640
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
]]>