Changing Datasource | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Changing Datasource

Hi, Following is a simplified version of my DTS package 1) SQL task picks list of 30 servers from CONNECTION1
2) Active X scripts set the DataSource for CONNECTION2 from the list
prepared in step 1
3) SQL Task is executed on CONNECTION2
4) SQL Task is executed on CONNECTION1
5) Active X scripts loops back to step 2
Every thing works fine except that step 3 executes the SQL Statement on very
first server 20 times. I have checked DataSource value by inserting
necessary msgbox , I get correct result but for some reason SQL Task is
being executed on the very first entry. To me it seems that connection info
is being cached. Is there a way to refresh the cache. In step 2 instead of setting the DataSource through vbscript I have tried
setting Datasource using Dynamic Properties Task using Globalvariable value
for DataSource. Somehow I am not inclined to write vb application or vbscript to achive the
goal.. One last thing, when I executes steps manually it works fine.
Thanks Deepak Khattar
Deepak Khattar
World Health Organisation
Geneva, Switzerland