One package run over diferent database

Question

I have multiples database, all with the same structure
For each database I have 8 store procedures, this procedures load data from this database over other named DATAMART.

the number of source database is variable in each customer and in each database I need run this 8 store procedure for then run the analisys service proyect.

Is possible make this using IS package?

Best regards


Jaime

Answer 1

This is definately possible.  You could use a query (which you would run against each customer's server) to find the total number of databases on each server.  You could then insert the database and / or server names into a Configuration table (one that you create that includes the server, database names, and even tables if you need them).  The last step would be to create a "For Each Loop" container to pass the name of the server and database into a connection string to make it dynamic.  For each database that is passed into the "For Each Loop" you could execute the stored procedure.

This way you don't ever have to know the names of the databases beforehand.  If the number of databases does not change for each customer, you could even hard code this into a configuration table.

It seems like this comes up a lot so if you need more information or an example then I'll write a Blog with screenshots.

Let me know if this helps.

---

Check out my blog!

Answer 2

Michael:

Thanks for you help.
I'm starting to work with Integration Services.
All of the above is within the same package?

Best regards

Jaime

Answer 3

Yes it is possible in the same IS package.

As suggested by michael implement foreach loop and identify the DB's which comply your structure and execute the SP's using EXECUTE SQL Tasks available in the control flow tab. Repeat this activity for all the DB.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 4

Here is an example of looping through a dataset which hopefully should give you a good starting point.

http://www.codeproject.com/KB/database/foreachadossis.aspx

---

every day is a school day

Answer 5

Dear Michael:

Please write one example in a blog

Best regards

jaime