sql server dts: Extract data from many OLTP database -- sql server dts

Re: Extract data from many OLTP database

Allan Mitchell
6/5/2004 5:35:43 PM

sql server dts: You can do it like this if you want or you can create 10 DataPump tasks and
move the data that way. They can all be in the same package.

--
--

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

[quoted text, click to view]

Extract data from many OLTP database

Tristant
6/6/2004 12:36:46 AM

Hi All,
I need to do ETL to pull data from 10 OLTP database into one Olap database.
I do it by creating stored proc and call it from 'Execute SQL Task'.
from 'Execute SQL Task' => EXEC SP_UpdSalesman_Dim

=> Create Proc SP_UpdSalesman_Dim AS
Insert into Salesman_Dim
Select * from Server_1.Oltp_DB1.dbo.Salesman S1
Left Join Salesman_Dim S2 ON [S2.Keys] = [S1.Keys]
Where [S1.Keys] IS NULL

What is the best approach for this : should I create 10 Task like this ?
Or is it better I use Looping here and pass database name to the stored proc
?

Thank you for any help,
Trist