0

In my server there are multiple databases. Hence having multiple connection manager for each db on server. Would like to pass database name as parameter in executesql task, so that I can reduce the no of connection managers. Something like below would be most preferable,

use ? 
select * from tablename
vignesh
  • 1,414
  • 5
  • 19
  • 38
  • 1
    Sounds awful... I guess the solution with multiple con. managers (or at least multiple parameters which are interpreted as connectionstring by the expression on one con. manager) would be better... anyways: it should be possible to put an expression on your execute sql task and there you can build the statement like `"USE " + [parameter] + "; SELECT *..."`... – Tyron78 Mar 02 '21 at 07:24
  • yes I have tried that working – vignesh Mar 03 '21 at 03:30
  • with out expression any way we could configure database name at runtime of any task. I hope it is not available. – vignesh Mar 03 '21 at 03:32
  • No, as far as I know there is no other possibility. Even passing the parameter to a SQL Task with dynamic SQL won't succeed. – Tyron78 Mar 03 '21 at 12:24

1 Answers1

0

You should be able to use multipart database naming conventions. the "use database" just changes the context of your connection. Choose the database that you use most frequently as a default then use the following:

select column1, column2 from databasename.schema.tablename

You can even use it across servers if you setup linked servers. (4-part naming)

select column1, column2 from linkedserver.databasename.schema.tablename

Mike Munoz
  • 1
  • 2