Re: moving data between backends (fast connectivity need)

DTS truly is simple and fast indeed (10-15k recs/sec move on a laptop, in a vmware machine, both Ora and M$ and BIDS loaded in vmware).

My problem is having tons of tables and many field changes every now and then. I have to manually invoke the dtswizard every time and that's a pain, I'd much rather write a script go scoop the dictionary and move my data that way, rather than the point and click dtswizard .... or maybe I need to automate the dtswizard...hmm, that could be an idea (talking out loud to myself)

My problem is that using the LinkedServer on the MS SQL Server (2005) is painfully slow....Is using a LinkedServer indeed the de-facto slow method?

My initial reasoning was that I want to remove the middle tier SSIS/DTS if I can.... technically, reducing the number of tiers should be fast(er)... but practically, at least in this instance, that does not seem to be the case.

thx much Alex -- any additional thoughts? ;-) Cosmin

Alex Gorbachev <ag_at_oracloid.com> wrote: Not that I'm favoring M$ tools too much but I heard that DTS is pretty simple and fast. At least on our clients is using it to pull data from Oracle and they don't complain.
I think they use Oracle ODBC driver.

On 11/15/07, Cosmin Ioan wrote:
> hello everyone,
> I'm trying to move data back and forth between MS SQL and Oracle and
> apparently to do so, **from** MS SQL to Oracle, there's some fast connectors
> from persistentsys.com
>
> If I try to move data **from** Oracle **to** MSSQL, via a LinkedServer (in
> MSSQL) using MS OLEDB drivers for Oracle (via inserts), it seems
> excruciatingly slow (even with columns specified) versus using SSIS via the
> data flow components (5x slower). Same goes for the Oracle OLEDB drivers for
> MS and connecting from within Oracle to MSSQL.... pretty slow.
>
> I don't want to get involved with yet another ms tool --if I can help it --
> nor introduce a middle tier (SSIS) and was wondering whether anyone has had
> any experience and can provide any feedback on any fast drivers or setup
> (again, to move data **from** Oracle **to** MSSQL), be them OLEDB, ODBC,
> third party connectors or ....Oracle Gateway?? -- did not do any research
> at all into the latter...
>
> thx much for any feedback,
> Cosmin
>
>
>

-- 
Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
http://www.pythian.com/blogs/author/alex http://www.oracloid.com
BAAG party - www.BattleAgainstAnyGuess.com


--
http://www.freelists.org/webpage/oracle-l