August 20, 2003 at 2:08 pm
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 Table1
Server A Insert x,y,z into Table2
Server A Insert a,b,c into Table3
Where Name Not in (Select NAME from ServerB...Table4)
etc...
Thanks, Dave
August 21, 2003 at 4:05 am
Are you doing a Transformation task at all or just a sql query. Where does a,b,c,x,y,z values come from to be inserted. If it is a data transformation you can do a LOOKUP on each column transformation and write a sql query to execute at the same time.
August 21, 2003 at 6:23 am
It's a combination of transformations and sql queries, inserts and updates. I did some more research and assuming I understand things correctly, SQL Server automatically tries to execute DTS code in parallel provided I am not using any precedence constraints. Does this sound correct?
Thanks, Dave
August 21, 2003 at 7:08 am
yes that is true although it will only try and you still need multiple connections as only one connection object can be used at a time(so with one connection object runs serially). You can still set an activex script with workflow to run several tasks in parallel this is better to use because you can control commit and rollback when one task fails.
August 21, 2003 at 7:27 am
I'll look into your ActiveX recommendation. Any tips on you to handle DTS code migration from Test to Production? I'm hoping there is a way to avoid having to change multiple connection properties for the destination of the data. I believe the source server connection properties will automatically change when I save the package to the production server, but I can find no way around having to visit each connection property for the destination server.
Thanks again, Dave
August 21, 2003 at 7:49 am
if you are going to use an activex script to branch into all you parallel tasks in the script you can set the source and destination propeties of the connection objects to a Value stored as a global variable. then you only have to change the gv value once.
Edited by - MikeTomkies on 08/21/2003 07:50:48 AM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply