January 3, 2007 at 11:55 am
Hi,
I am in the process of creating a generic DTS package that will ultimately be called by multiple client applications simultaneously. The DTS package has several global variables that will need to be set to different values depending on the calling application. The main purpose for the variables is to dynamically set database connections and file paths. Each client has its own database.
Will there will be any conflicts if several clients are calling the same DTS package and setting global variables at the same time? Does each client open up a separate instance of the package?
Any thoughts/feedback would be greatly appreciated!
Thanks.
January 3, 2007 at 5:02 pm
I don't think it will be a problem but you can the load test by creating the multiple jobs for different client to run at the same time.
MohammedU
Microsoft SQL Server MVP
January 4, 2007 at 11:18 am
A separate instance would be opened each time, but like the poster above me said, don't try it without stress testing it. The likeliest source of potential problem is if the package updates any shared data or databases.
Make sure that there is some mechanism to make sure no one is able to run the package against a database other than their own. For example, have them pass in their login/password and then have the package lookup their global settings in a central database.
January 5, 2007 at 7:11 am
We have been stress testing as you suggested and there have been no issues so far. It helps very much to know that a separate instance is opened each time.
Thank you.
January 5, 2007 at 11:29 am
Thanks for the update. Please let us know the final outcome. I've never used DTS packages in this manner. I've only ever intended them to have a single instance running at a time, though I have had multiple running unintentionally with no problems other than both instances trying to update the same tables.
January 5, 2007 at 12:43 pm
We have had DTS packages in production for 3 or more years that regularly execute the same DTS package and send in global variables to change which server the connections hit. Our main import process has the same DTS package running 6 times with each instance hitting a different server.
So, once you know that the number of instances of the DTS package you expect to be running at the same time doesn't cause you performance problems then you will have no problems.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 5, 2007 at 3:17 pm
Thank you for the real-life example. This gives us much more confidence going forward.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply