Only you can say which performs better as we are not there but personally I
would go with #1. The amounts of data you are using is not going to be a
big deal.

--
--

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 All,

I am doing ETL using DTS
I have two servers :
Server_1 : OLTP Server (SQL2k)
Server_2 : OLAP Server (SQL2k)
my biggest dimension tables could be 5000 rows and my Fact tables could be
300.000 - 400.000 records.

Which one from these two options will be better for ETL (e.g : table
Salesman_Dim) ??

1) Option 1, steps : (NO Linked Server required)
- Truncate Salesman_Temp
- Extract all rows into Salesman_Temp with 'Transfer Data Task', then
- 'Execute SQL Task' =>
Insert into Salesman_Dim
Select * from Salesman_Temp ST
Left Join Salesman_Dim SD ON [SD.Keys] = [ST.Keys]
Where [SD.Keys] IS NULL

2) Option 2, Linked Server is required
=> Create Proc SP_UpdSalesman_Dim (@Srvr Char(10), @db Char(10))
AS
Declare @SqlStmt Varchar(1000)
SET @SqlStmt =
'Insert into Salesman_Dim '+
'Select * from '+@Srvr+'.'+@db+'.dbo.Salesman S1 ' +
' Left Join Salesman_Dim S2 ON [S2.Keys] = [S1.Keys] '
'Where [S1.Keys] IS NULL '
EXEC (@SqlStmt)
GO
=> add 'Execute SQL Task ' => EXEC SP_UpdSalesman_Dim ?, ?
=> Pass GlobalVariables as Input Parameters

Which options is better and giving better performance ?

Thanks for your help,
Trist