I'm designing a DTS package right now to pulls partial data from
several tables in a database on a server which I have limited access to
(read only, for the most part) to a server and database I'm the owner
of.

I am able to create Data Pump Tasks to import table by table to each
corresponding table on my server. It would be more efficient for me,
and I assume for the DTS process, if I could just write T-SQL
statements to select the data I need into the tables I want instead of
creating a new pump for several tables.

Is it possible to do this without running sp_addlinkedserver on the
database which I don't have write access to? I'm accessing the data
using Windows Authentication and cannot seem to link to the server from
the one I have ownership rights on.

Thanks for any input. Sorry if my question is very facile, please
point me to the right BOL section or KB article, if so.

Hello mahalie (AT) gmail (DOT) com,

You could try using OPENDATASOURCE()

I am not sure that this is any more efficient that the Transaform Data Pump
tasks though. What is essential is when moving data only move the data you
need and this usually menas specifying a SQL Statement instead of just the
table object.



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks for the suggestion. I ended up doing a datapump for each
table...using specific SELECT statements

Just to valididate your advice, I am just learning SQL and was tasked
with changing the datasource of an existing search application that
relied on a DTS package. As a quick fix I mainly modified the DTS
package changing the table object access method to SQL statements so I
could create aliases that would work with the existing application
code. In the process I only grabbed fields I knew were used.
Afterwards the application ran significantly faster, everyone was
really impressed...I'd like to say I knew what I was doing but it was
mostly a lucky side effect. (The main table was had a full text index
so it made a huge difference when I only pulled down 4 columns instead
of 24!)

So, one more quick clarification. Is there a problem with using
several datapumps on one set of connection objects, that is I could
either keep adding the existing source connection and the existing
destination connection for each new datapump which is visually a little
easier to look at, or I can just keep adding datapumps for every table
I need (this is a highly normalized db with a lot of little config
tables) to the exisitng connections in the designer. Does it make any
difference?

~Mahalie