Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2011-08-24 : 08:48:14
|
| Hello All,I have to get database details for reach database on the server. In my SSIS package, I am already having a SQL task which get the dbnames in a result set. How do I dynamically change the dbname in connection string in my ForEach loop before it executes another SQL task which is database dependent.Thanks much, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-24 : 12:15:35
|
| store the resultset in a variable of type object. Add a for each loop to iterate over ADO.NET recordset variable and then add another string variable to get each value from list. then pass that variable to another variable which concatenates it with rest of static part to get full connection string. then map that connection string variable to your tasks connection string using expression builder.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-08-27 : 20:57:11
|
| Thanks visakh16,I created a global veriable and concatenated constant conntion string and @[User::DBName]. Then in the SQL Task under expression I chooose Connection and the global veriable name. Then executed the package but received an error. Am i doing anything wrong? Do you have a dtsx file to share? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-28 : 01:56:34
|
| whats the error you're getting? did you test the expression inside expression builder for variable?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-08-31 : 09:20:33
|
| I made this work by creating another connection called 'Dynamic connection' and then in the Expressions property I am defining the ServerName and DBName. So far this is wokring fine.But now I have to run SQL commands on all the servers-databases. And save their output in a table on one of the server.Since there are no linked servers, I have to connect to servers using dynamic connection and then get the SQL result set in excel spreadsheet and then import that in a table.I have started it with Data Flow Task but I don't know where to set the database connection? Can someone please help me?Thanks,-P |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-31 : 10:15:31
|
| you need to create a source connection to point to your source server and also excel destination for getting result to excel spread sheet------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Change DBName dynamically in connection string