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
Post by Charles Wang [MSFT]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/library/aa933469(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
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/aa948874.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================