Hi everyone,
I'm trying to create a package to import data from Oracle to SQL Server
2000. For the records being imported, if they already exist in SQL, then
UPDATE them, otherwise INSERT them.
Can someone show me how this can be achieved please. I think I need to
somehow dump the data from Oracle into a temporary SQL table before
Update/Insert the actual tables and I can't seem to find a way of doing this.
Any help is greatly appreciated.
Many thanks,
Calvin

There are a few ways you may like to attack this

1. Pump from Oracle to SQL Server and use the Data Driven Query Task (DDQ).
This will allow you to determine what to do for each row and you then call
the relevant statement. This is slow on large datasets
2. Use a Transform Data Task and use lookups. see #1 for downsides
3. Pump the necessary rows to a staging table from Oracle to SQL Server.
Now issue the correct TSQL statements
4. For #3 there are a number of ways to get the rows from Oracle to SQL
Server. Some say the drivers do not move data quick enough and have
resorted to

Export to Text File --> FTP --> BULK INSERT

Hopefully that has given you a coulpe of ideas

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Calvin KD" <CalvinKD (AT) discussions (DOT) microsoft.com> wrote

Thanks so much for your reply Allan. I did try Data Driven Query task with
ActiveX Scripting but it keep giving me error on Update. Insert was OK though
and I could not debug as to what the reason was so I scrapped it. Also with
this method, I would have to scan the whole table for each incoming record
for its existence before I can issue an UpdateQuery or an InsertQuery, which
I thought would rather be inefficient.
I had to resort to Transform Data Task by manually creating temp tables
before binding. Once all the data has been mapped, I had to create 3 Execute
SQL Tasks, one to create temp table, one to Update/Insert and the other one
to drop the temp tables. That seems to work OK.
I'm not quite sure what you mean by lookups. If there's a better way of
doing this, would you care to show me how.
Thanks so much Allan,
Calvin.

"Allan Mitchell" wrote: