Importing Dynamic Table Name from Oracle Help

#1

I've a job that needs to download a table from Oracle, which table name is stored in an Oracle table. I can use dynamic sql in Oracle, so it can have the same name every time, which needs a lot of space and sounds very stupid. Is there a better way of doing it? Thanks in advance for your help

Exampl

Cyclic Table in Oracl

table_name mont
table01 20040
table02 20040
..
..
..
..

#2

As long as the structure reamins the same you simply need to rename the
DestinationObjectName of the DataPump task.
You can do this dynamically using

Dynamic Properties Task
Active Script task (If you can script the logic behind table naming)

Changing the DataPump Source and Destination Tables
(http://www.sqldts.com/default.aspx?213)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Dave Wong" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I've a job that needs to download a table from Oracle, which table name is
stored in an Oracle table. I can use dynamic sql in Oracle, so it can have

the same name every time, which needs a lot of space and sounds very stupid.
Is there a better way of doing it? Thanks in advance for your help.

Quote:

Example

Cyclic Table in Oracle

table_name month
table01 200401
table02 200402
..
..
..
..

#3

Thanks! But I only need to select certain fields from the Oracle table, how do I put the varable into the SQL for Oracle
ie. In the SQL box, I usually put in select field1, field2, field3 from table01 where cust_segment = 'MASS' (table01 is the tablename being selected from the cyclic table

----- Allan Mitchell wrote: ----

As long as the structure reamins the same you simply need to rename th
DestinationObjectName of the DataPump task
You can do this dynamically usin

Dynamic Properties Tas
Active Script task (If you can script the logic behind table naming

Changing the DataPump Source and Destination Table
(http://www.sqldts.com/default.aspx?213

--

---------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP
www.allisonmitchell.com - Expert SQL Server Consultancy
www.SQLDTS.com - The site for all your DTS needs
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or

"Dave Wong" <anonymous (AT) discussions (DOT) microsoft.com> wrote in messag
news:5100D506-C822-43DA-AFA7-949F6A3298AA (AT) microsoft (DOT) com..

Quote:

I've a job that needs to download a table from Oracle, which table name i
stored in an Oracle table. I can use dynamic sql in Oracle, so it can hav

the same name every time, which needs a lot of space and sounds very stupid
Is there a better way of doing it? Thanks in advance for your help

Quote:

Exampl
Cyclic Table in Oracl
table_name mont
table01 20040
table02 20040
.
.
.
.

#4

OK then that is the SourceSQLStatement property of the DataPump task.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Dave Wong" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Thanks! But I only need to select certain fields from the Oracle table,
how do I put the varable into the SQL for Oracle?
ie. In the SQL box, I usually put in select field1, field2, field3 from
table01 where cust_segment = 'MASS' (table01 is the tablename being selected

from the cyclic table )

Quote:

----- Allan Mitchell wrote: -----

As long as the structure reamins the same you simply need to rename
the
DestinationObjectName of the DataPump task.
You can do this dynamically using

Dynamic Properties Task
Active Script task (If you can script the logic behind table naming)

Changing the DataPump Source and Destination Tables
(http://www.sqldts.com/default.aspx?213)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Dave Wong" <anonymous (AT) discussions (DOT) microsoft.com> wrote in message
news:5100D506-C822-43DA-AFA7-949F6A3298AA (AT) microsoft (DOT) com...
I've a job that needs to download a table from Oracle, which table
name is
stored in an Oracle table. I can use dynamic sql in Oracle, so it
can have
the same name every time, which needs a lot of space and sounds very
stupid.
Is there a better way of doing it? Thanks in advance for your help.
Example
Cyclic Table in Oracle
table_name month
table01 200401
table02 200402
..
..
..
..

#5

Thanks Alan. I'm fairly new to SQLServer, have problem embedding the table name into the SQL pass to Oracle. Looking up the books right now :-(

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Importing Dynamic Table Name from Oracle Help

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Importing Dynamic Table Name from Oracle Help - 03-01-2004 , 01:46 AM

Re: Importing Dynamic Table Name from Oracle Help - 03-01-2004 , 02:01 AM

Re: Importing Dynamic Table Name from Oracle Help - 03-01-2004 , 03:06 AM

Re: Importing Dynamic Table Name from Oracle Help - 03-01-2004 , 04:12 AM

Re: Importing Dynamic Table Name from Oracle Help - 03-02-2004 , 04:26 AM