Re: moving data between backends (fast connectivity need)
- From: "Alex Gorbachev" <ag@xxxxxxxxxxxx>
- To: cosmini@xxxxxxxxxxxxxxx
- Date: Fri, 16 Nov 2007 10:09:42 -0500
Here is what response I've got.
I hope that helps. Some of it looks specific to particular environment.
---------------------------
Tell him to run the wizard but instead of running it to save it as a
DTS package. Then after the package is saved, he should open it and
then Save it as VB Script. After that he can look inside the file
using Notepad and see how the package is created using VB.
The file will look like the one attached. Inside this file it is used
the DTS object model and the package gets created, connections get
created, assigned, and tasks/data pumps are also created sing VB.
After everything is created the select statement is created and the
target table.
What I did is I copied that script, modified to serve my needs and
used in a Microsoft Project with a connection the SQL server.
The problem with this kind of file is that the tasks are not creted
using loops, they are created one at a time, so your friend should put
that in a loop.
There should be 2 loops:
1. One that loops through the system tables (SYSOBJECTS) or a custom
table containing table names if not all tables are needed.
2. Second one that loops through the columns for the looped table
above, looking at SYSCOLUMNS joined with SYSOBJECTS.
The package also saves the DTS on the specified SQL server.
---------------------------
On Nov 15, 2007 7:35 PM, Cosmin Ioan <cosmini@xxxxxxxxxxxxxxx> wrote:
> Alex, that's **exactly** what I'm doing (as many transformations as possible
> on the Oracle side) then simply, moving the end point data to MS!!
>
> I've looked at dtswizard and I don't see the possibility of scripting
> that.... maybe I'm looking at the wrong utility -- dtswizard.exe -- can
> you check for me if there's another tool they used -- command line -- that
> gives you some flexibility to script? I'll probably need to check the ms
> forums ;-)
>
> thx much,
>
> Cosmin
>
> Alex Gorbachev <ag@xxxxxxxxxxxx> wrote:
> As far as I know they have done some scripting n DTS and schedule
> those jobs at night time without any interactions. Maybe I'm missing
> something - I don't really know that tool myself.
>
> Another point - wouldn't it be efficient/possible to do
> transformations on Oracle side?
>
>
>
> On 11/15/07, Cosmin Ioan wrote:
> > 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 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
> >
> >
>
>
> --
> Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
> http://www.pythian.com/blogs/author/alex http://www.oracloid.com
> BAAG party - www.BattleAgainstAnyGuess.com
>
>
--
Alex Gorbachev, Oracle DBA Brewer, The Pythian Group
http://www.pythian.com/blogs/author/alex http://www.oracloid.com
BAAG party - www.BattleAgainstAnyGuess.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
