SQL Server Performance

Changing Datasource

Discussion in 'SQL Server DTS-Related Questions' started by deepak_khattar, Feb 10, 2004.

  1. deepak_khattar New Member

    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
    DBA
    World Health Organisation
    Geneva, Switzerland

Share This Page