Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2003-08-20 : 16:05:27
|
| How do I run multiple SQL commands in parallel within one DTS package? I know I can create multiple connections per command, but this can be a pain to maintain. Server A Insert x into Table1Server A Insert x,y,z into Table2Server A Insert a,b,c into Table3 Where Name Not in (Select NAME from ServerB...Table4)etc...Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-20 : 16:08:33
|
| Just create multiple "Execute SQL Tasks". You would need three of them, plus you would need one connection. None of them would be connected.Tara |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-21 : 07:56:11
|
| There are two things to concern yourself with. First, in the package properties, you need to set the "Limit the maximum number of tasks executed in parallel to:" appropriately. Second, you can only execute one command-per-connection at a time. So if you want to run 5 "Execute SQL Tasks" at the same time, you'll need to set the "Limit the max...." to 5, and you'll need to have 5 connections and 5 tasks, each task using a different connection.If you have 5 execute sql tasks and 1 connection, it will only execute one task at a time, and if not dictated by your precedence constriants, it will usually fire them off in the order of the task name.Jay White{0} |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-08-21 : 08:44:25
|
| Jay,Thanks for the explanation. I did some more reading in BOL and found what you indicated. Any suggestion on how to handle code migration? If I have a DTS task with 7 connections and the connection properties are controlled from INI files defined in one Dynamic Properties Task, I have to modify all 7 path statements within the Dynamic Properties Task when I migrate my DTS code from Test to Production. Is this normal practice or am I doing things the hard way?Dave |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-21 : 09:45:30
|
| I don't think there really is an easy way.If you want to make the process repeatable, create an activex task at the start of your package that creates new connections and dynamically sets your other tasks to use those new connections.Jay White{0} |
|
|
|
|
|
|
|
|
DTS Tasks in Parallel