Export Data From SQL Server 2K to Oracle Using ADO Object in VBS

Hi to everybody,
I'm trying to write a vbs (into a DTS in SQL Server 2k) that reads the
content of a table located in SQL Server 2000 and then tries to INSERT
it into another table in ORACLE. I've created the connections to
Oracle and SQL Server 2K and they work fine given that I can
manipulate information on both programs. However I'm unable to merge
the "select from SQL 2K" and the "insert into Oracle" into a single
statement.
Any help will be wellcomed.
After many workarounds, this is the last version of the code I've
used:
Function Main()
Dim oCn_ODS, oRs_ODS
Set oCn_ODS = CreateObject("ADODB.Connection")
oCn_ODS.Open "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE =DitoNano;"
Dim oCn_ORA, oRs_ORA
Set oCn_ORA = CreateObject("ADODB.Connection")
oCn_ORA.Open "Provider=MSDAORA.1;Password=testpwd;User ID=test;Data
Source=parrot.gouki.com; Persist Security Info=False;"
Set oRs_ORA = oCn_ORA.Execute("CREATE TABLE employee_v (
nest_doc_id CHAR(10) NOT NULL,
c_nest_id CHAR(10) NULL,
id_suj CHAR(10) NULL,
c_cereal VARCHAR2(150) NULL) ")
Set oRs_ODS = oCn_ODS.Execute("SELECT * FROM DitoNano_Nest")
Set oRs_ORA = oCn_ORA.Execute("INSERT INTO employee_v (" &
oRs_ODS.Fields & ")")
Set oCn_ORA = Nothing
Set oCn_ODS = Nothing
Main = DTSTaskExecResult_Success
End Function
Greetings,
David Grant
Hi David,
How many records are you importing, and how good is the data?
if there are loads of records and the data is good (by that I mean you
don't have to verify any of it during the import). Look into the
'Insert Into' command for the above example you are looking at
something like
insert into oRS_ORA from oRS_ODS, of course the fields have to match in
both tables and primary keys may become a problem. This would only work
if you can somehow link the ORACLE database into SQL so that they come
from the same connection using the linked servers then the command is
going to look like 'insert into mylinkedsrv.oRS_ORA from oRS_ODS' .
If the above is not posible then you may have to 'scroll' through each
record on the export side and insert into the import side, but this
will be many times slower, but this gives you the posibility of
verifying the data on the way through.
Other posters may be able to give you a better solution as I am not
very good with ORACLE, SQL is my baby.
Hope it helps.
Regards
Alex
|||Hi David,
How many records are you importing, and how good is the data?
if there are loads of records and the data is good (by that I mean you
don't have to verify any of it during the import). Look into the
'Insert Into' command for the above example you are looking at
something like
insert into oRS_ORA from oRS_ODS, of course the fields have to match in
both tables and primary keys may become a problem. This would only work
if you can somehow link the ORACLE database into SQL so that they come
from the same connection using the linked servers then the command is
going to look like 'insert into mylinkedsrv.oRS_ORA from oRS_ODS' .
If the above is not posible then you may have to 'scroll' through each
record on the export side and insert into the import side, but this
will be many times slower, but this gives you the posibility of
verifying the data on the way through.
Other posters may be able to give you a better solution as I am not
very good with ORACLE, SQL is my baby.
Hope it helps.
Regards
Alex
|||Why do this through a piece of vbs?
All I can see is that you are selecting from SQL Server and putting into O. Is that right?
If it is then a better fit would be a DataPump task wouldn't it?

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"David Grant" <icebold54@.hotmail.com> wrote in message news:18503386.0504180745.5101578f@.posting.google.c om...
> Hi to everybody,
> I'm trying to write a vbs (into a DTS in SQL Server 2k) that reads the
> content of a table located in SQL Server 2000 and then tries to INSERT
> it into another table in ORACLE. I've created the connections to
> Oracle and SQL Server 2K and they work fine given that I can
> manipulate information on both programs. However I'm unable to merge
> the "select from SQL 2K" and the "insert into Oracle" into a single
> statement.
>
> Any help will be wellcomed.
>
> After many workarounds, this is the last version of the code I've
> used:
> Function Main()
> Dim oCn_ODS, oRs_ODS
> Set oCn_ODS = CreateObject("ADODB.Connection")
> oCn_ODS.Open "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE =DitoNano;"
>
> Dim oCn_ORA, oRs_ORA
> Set oCn_ORA = CreateObject("ADODB.Connection")
> oCn_ORA.Open "Provider=MSDAORA.1;Password=testpwd;User ID=test;Data
> Source=parrot.gouki.com; Persist Security Info=False;"
>
> Set oRs_ORA = oCn_ORA.Execute("CREATE TABLE employee_v (
> nest_doc_id CHAR(10) NOT NULL,
> c_nest_id CHAR(10) NULL,
> id_suj CHAR(10) NULL,
> c_cereal VARCHAR2(150) NULL) ")
> Set oRs_ODS = oCn_ODS.Execute("SELECT * FROM DitoNano_Nest")
>
> Set oRs_ORA = oCn_ORA.Execute("INSERT INTO employee_v (" &
> oRs_ODS.Fields & ")")
> Set oCn_ORA = Nothing
> Set oCn_ODS = Nothing
>
> Main = DTSTaskExecResult_Success
> End Function
>
> Greetings,
> David Grant