Hi,

SQL Server 2000 Professional
SP4
Windows Server 2003

What I'm doing: I have one dts package that generically pushes data from
one db table to another db's table (both dbs have same table name)

Package Location: Server

Column mapping is done by field name and not ordinal position (my pump)

This DTS package is executed for many tables, one at a time, and each table
must complete before the next is started (slow and synch) ... so each run of
the DTS package completes before the next call for the next table is
started...

The question: Can I move the calls to run my DTS package into a .net
executable that has many threads, and each thread starts an instance of my
dts package with one table name? I know how to execute DTS from .Net....

The real question: Can I run the same DTS package many times at the same
time?

The goal: This would allow the process to complete much faster (pump all
tables at same time on different threads using same DTS Package)

I hope this question makes sense.

--
thanks in advance,
georgejetson

Hi George,
Welcome to Microsoft MSDN Managed Newsgroup. My name is Charles Wang[MSFT].
It is my pleasure to work with you on this post.

From your description, I understand that you would like to know if it is
feasible to run your same DTS package in multiple threads in your .NET
application at the same time.
If I have misunderstood, please let me know.

It depends on what your DTS package is designed. It looked that your DTS
package need to first start loading data from the tables one by one in your
source database into the destination database.

Could you please let me know why you have such requirements? In this case,
each thread will do the duplicate job and is it your expected result? Using
multiple threading may not be helpful since all threads start running the
DTS package from the same start point which is actually a specific table.
This method will impact the loading performance on the source server and
may also cause conflicts in the destination table.

If you want to take advantage of the multiple threading programming, I
recommend that you use parameterized query in your DTS package so that each
thread can pass different parameters to the DTS package and get different
tables for exportation in each thread. This method can take the advantage
of parallel running if you have multiple CPUs.
http://msdn.microsoft.com/en-us/libr...9(SQL.80).aspx

Hope this helps. If you have any other questions or concerns, please do not
hesitate to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi George,
Just check with you what this issue is going on?

If there is any issue, please feel free to post back.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks for your reply, and sorry for the delay.

Ok, here is what I'm trying to do, please let me know of a better way :-),
there must be a better way!!!

I have multiple lans all over the world each running an instance of a large
application.
Each lan may have a different software release of ours, and thus, a
different schema in various tables.
Each application has ONE sqlserver database.
Each lan is very private, very secure. I can't connect from home base.

One golden rule: We don't remove fields in tables, only append to them.

Anyways, during a software upgrade, we do the following
a) backup the current database (REAL_DB)
b) restore the backup as another database (RELEASE_DB)
c) restore the new software release database (additional schema changes,
etc) over the old db, this db has the lastest and greatest schema obviously
without any customer data
d) push application specific data from RELEASE_DB to REAL_DB

We do this via a generic dts package we wrote that is passed a specific
table name
It simply loops thru each field in said table, and does a column mapping by
column name (not ordinal position)
for each field in RELEASE_DB.tableName
map field.Name to REAL_DB.field.Name
next field

once the mapping is done, we run the pump.

We call this generic dts package for many tables, one at a time, in a synch
fashion

This seems to work, and work well. But if we have lots of customer data, it
takes time (between 30 minutes and an hour).

Luckily, we don't upgrade software releases too often.

I was thinking if I could execute the same generic dts package from multiple
threads, passing in different table names, it would complete much faster.

For those tables that have pk/fk requirements, those would still run synch.

What am I missing, what's the right way to do this.

I'm clearly not a DBA.

thanks for your help and sorry for the delay

--
thanks in advance,
georgejetson


""Charles Wang [MSFT]"" wrote:

George,
Running your same DTS package with multiple threads will cause many errors
due to constraints and probably fail the threads. If you do not like use
parameterized query, you may consider separating your DTS packages into
multiple packages each of which contains a separate part of the tables in
the database. The tables in each package should not have any confliction on
constraints with other tables in other DTS packages. By this way, you can
exuecte the different DTS packages in different threads.

Also you may check your network utilization when your DTS package is
running, if it had reached to almost 100%, then it left less space for
performance improvement by using multiple threads.

Thank you!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======