I am creating a datawarehouse for the company I work at (I am an intern so nothing too fancy). They have multiple databases with the same database structure. I am creating a data flow task that uses an ODBC connection (because I am using MySQL) to retrieve a table from the database, do a little transformation and load it in the new database I have set up. When the entire flow is done, I have loaded the datawarehouse with data from one database. But the company has allot more database with the exact same table names etc. Is there an easy way to change the source database? I don't want to change all the ODBC sources each time I want to run the dataflow for a new database. I could also copy the entire data flow, paste it in another data flow, change all the sources and run it. This would mean if the company has 50 databases, I would have 50 data flows with the only difference being they have a different database name. But I was wondering if there is another way? I don't want to have to change all the sources each time, because when the database updates in the future, all the connections will have to be manually changed and be executed again.
Its better to change the database name (dynamically) in the Initial catalog(database) in connection string, according to your package.
Change the connection string by going to properties of connection manager then
EXPRESSIONS -> Add Property -> Initial catalog is your database name and assign it to the variable which will change dynamically
OR
If you want to change entire better to go with CONNECTION STRING property
Connection string for ODBC connection of database is as:
Driver={SQL Server};server=*****;uid=**;database=database_name;Pwd=password