Subhash
Hi Anurag,
Experts / MVPs have already addressed this issues about dynamically assigning the Connection details. Follow the steps below
1) Create a table in one of your Oracle database with following fields:
ConnectionDetails(UserID, PWD, ServerName)
2) Insert details of all servers you want to deploy
3) Open the New SSIS Package, Drag and drop an execute sql task, write the query (Select * from ConnectionDetails) to retrieve Conectiondetails into a ResultSet Variable say User::ResultSet of an Object type.
4) Drag and drop the For Each Loop container after the Execute SQL Task and configure details to retrieve each row
5) Store output column values in Package Variables say "v_UserID", "v_passwd" , "v_serverName"
6) Drag and Drop a DataFlow Task into the ForEach Loop Container, determine the source, destination and transformation mappings required.
7) Now assign these variables to connection manager whose detail should change dynamically via expression builder Say
ServerName = @[User::v_serverName]
UserName=@[User::v_UserID]
Thanks
Subhash Subramanyam