Re: SQL Object Transfer Task

#1

If you create the indices by hand then you will be able to make a DROP
script and a CREATE script. This will allow you to DROP them before moving
the data and then reapply after moving the data. If you are only moving 1 *
table then using the Transfer Objects task may be an overkill. Try a simple
datapump task.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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

"eguru007" <eguru007 (AT) yahoo (DOT) com> wrote

Quote:

Hi people!

I have a situation where I need to move table data from a variable source
server to variable target server. I am using SQL Object Transfer Task and

updating the task properties from Dynamic Property Object. It works fine.

Quote:

But I need to drop indexes on target tables before copy and recreate after
copy. After spening some time I found out that DTSTransfer_ScriptOption

Constant property can be used to make this happen.

Quote:

I have no clue how to compute this constant, BOL doesn't offer much help.

Any pointers please.

Eguru007

#2

Hi Allan
Thanks for your response. I am still not clear how should make this work. Let me answer few points you have raised.

I have the DDL to create indexes and I can use that at the end of the table copy using object transfer. But I would like to build index using transfer object because I have a include table name list and some times DBAs will copy only small group of tables using this list. It would be a great deal if I need to search through my big DDL file and copy one index at a time. There are more than 100 tables and around 180 indexes in a typical db.

So my question is is it possible to use DTSTransfer_ScriptOption and or DTSTransfer_ScriptOptionEx constant to make this work with in the transfer object

Hope I am making sense

Thanks
Eguru007

#3

I have never used these options myself but I can only see that these would
be available if you were coding everything using the DTS object model.

Seeing as they are equivalent to the SQLDMO options i would probably
leverage that.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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

"eguru007" <eguru007 (AT) yahoo (DOT) com> wrote

Quote:

Hi Allan,
Thanks for your response. I am still not clear how should make this work.
Let me answer few points you have raised.

I have the DDL to create indexes and I can use that at the end of the
table copy using object transfer. But I would like to build index using

transfer object because I have a include table name list and some times
DBAs will copy only small group of tables using this list. It would be a
great deal if I need to search through my big DDL file and copy one index at
a time. There are more than 100 tables and around 180 indexes in a typical
db.

Quote:

So my question is is it possible to use DTSTransfer_ScriptOption and or
DTSTransfer_ScriptOptionEx constant to make this work with in the transfer

object.

Quote:

Hope I am making sense.

Thanks,
Eguru007

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

Re: SQL Object Transfer Task

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

Re: SQL Object Transfer Task - 06-07-2004 , 02:49 AM

Re: SQL Object Transfer Task - 06-07-2004 , 09:06 AM

Re: SQL Object Transfer Task - 06-07-2004 , 10:27 AM