Hi all,

I need to do ETL/ data movement from sales data and some dimension tables
located on 10 database into OLAP database. The databases are located in two
servers. (e.g: Server_1 : 5 databases, Server_2 : 5 databases)
They are actually same tables name on each databases.

I am looking for best DTS approach / model to pull data from these many
databases, can any body give suggestion ?
Or should I just use stored procedure since all databases are SQL Server ?

Thanks for any help,
Trist

The fact that you have 2 servers and 10 databases should not be a problem.

Personally I would stage the data. I would take my required data from the
10 databases and stage it on the OLAP server in a seperate DB. I would then
transform and load from there.

If you need to filter the data retireved from one server by data on another
then you can use linked servers to do this.


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"tristant" <krislioe (AT) cbn (DOT) net.id> wrote

Hi Alan,
Thanks for your reply.

Based on that, another questions are :

1) Pulling data from 1 database, is it better use one package to pull
data from each database, so that I will have 10 packages ? OR one
package to pull data from all database in a loop ?

2) What are the benefits of stanging the data first ?

3) What is the best scenario to filter data retrieval using linked
server, By LEFT Joining TABles across server ?
Or using DTSLookups ?

This my first implementation,
Thank you for your help,
Trist




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!