Greetings Experts -
I am new to SSIS, and, currently I am trying to get versed in using it. I would like to ask for some guidance regarding implementing a solution to a data problem I have.
I have a over 50 store locations that each have their own SQL DB.
I have already used a migration tool to copy the DB schema of the Source DBs to the Master DB.
All the tables are the same for all the store DBs, however, the data is unique to each store (cust names, inventory, orders, etc).
I would like for the project to grab about 40 tables from each DB (all the tables would be the same) and merge/dump/convert the data into the Master DB. However, If for example Store1 is lets say Texas1 and Store 2 is Texas2, I would want the data sets segmented as such where I can query for Texas1 or Texas2 or all at the same time.
Ultimately, the end goal is to have a process in place that will every day pull the data from the store DBs and insert them into the Master DB so we can run queries for reporting.
If using SSIS is not the best solution, please advise on the route I should take.
If my logic is flawed, please do not hesitate in correcting me. I appreciate constructive criticism.