Im trying something similar to my previous problem.Get list of database name from each server based on list of server name from a table and dump results to flat file.For testing purpose I only have 1 entry in serverlist table that uses SQL authentication to connect.
Here is my problem.Everything run's well no errors BUT no results are captured and upon opening MultiServer connection manager, no entry in password field even when save password is ticked.
1) Create table serverlist [name,ipadd,active,username,password,connectionstring]
2) Create a SQL Task 'select connectionstring from serverlist where active=1'
3) Store results in var ConString of type object
4) Create for each loop,Enumerator= Foreach ADO, ADO source variable= ConString, variable mapping= SrvCon of type string Index=0
5) Create data flow task within For Each Loop.
6) In data flow task, Define Olde db source, where ole db connection mng = MultiServer, access mode = SQL Command ,SQL Command Text = use master
select name from sys.databases
7) From ole db source to flat file connection manager as destination
Configuration for MultiServer oledb connection manager
1) Expression connection string = var SrvCon
Connection string in table serverlist is saved as such
Data Source=126.96.36.199;User ID=user;Password=pass;Initial Catalog=master