SSIS Package Project

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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I think you are on the right track using SSIS.  Can you tell me a little more about how you intend to use the data on Master?  My assumption is that you will use it for reporting purposes in which case I would recommend going the extra mile to create a dimensional data warehouse instead of just copying the tabular data over.
sj77Author Commented:
Thanks for the info, Brian.

We intend to use the data to derive meaningful information for our business users. For instance, the master DB will contain specific tables as explained above that will allow us to query the tables needed to generate reporting data.

For instance: sales numbers, GPM, GP, and more detailed data that can be found in those tables.

Let me know if you need me to further clarify, will be happy to do so.

You know, I have never done that myself, and honestly, no one here on my team has either. So I am going to be going off on a limb and learning how to build a data warehouse if you deem it being the best solution here. Do you have any pointers, articles, tutorials regarding how to do this? I am scouring the internet as we speak.
Brian CroweDatabase AdministratorCommented:
I just built my first dimensional data warehouse a few months ago.  I will admit I was a little intimidated at first but it wasn't nearly as painful as I anticipated.  Based on the facts that you are pulling from identical schemas to a central repository and you are copying from SQL to SQL it really shouldn't be too bad.

I strongly recommend grabbing a copy of "The Data Warehouse Toolkit" by Kimball/Ross.  This is pretty much considered the bible on the topic and it's not too painful a read.  Likely there is a targeted chapter that applies fairly closely to the solution you are looking for.  There is one on retail that sounds pretty close to what you are doing.  Also I would recommend browsing the articles on the Kimball Group website.

If done correctly it should take you longer to design the data warehouse than to actually build it.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

sj77Author Commented:
Brian -

Awesome stuff. I will be purchasing that today. Also, thank you for the article links. I am sure it will steer me in the right direction.

I found two of the same (I assume) books on amazon. Thoughts on which one I should get? See enclosed image below.

Brian, would you say that your data warehouse encompassed the general idea I am trying to accomplish? Or was your scenario different?
Brian CroweDatabase AdministratorCommented:
My situation is similar though on a smaller scale in that I am pulling primarily from a single OLTP database (primarily shopping cart data) to a data warehouse.  We are still in the process of "growing" the data warehouse to replace an outdated tabular model that we were using.  Handling 50+ stores shouldn't be a huge hurdle though I would have to think on how I would set that up to maximize package reuse.

For instance you won't want to build 50x(however many tables you are populating) packages.  Instead you would want to have however many packages as it takes to pull in a single store and then just call it once for each store by passing in a project level parameter or putting it in a for loop or something like that.  I'm not sure of the best way to handle that offhand.

I believe you are just looking at different editions of the same book.  My book is third edition which I believe is the current one.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sj77Author Commented:
Great information. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.